Since the release of SQL 7, SAP R/3 has one of the best database platforms on Windows NT/2000. This article is a description of the tight integration of SAP R/3 and SQL Server 7, and will focus on the parameters to check for your SQL installation to make sure your system works well. Many SQL administrators do not know SAP R/3 and are confronted to a new experience, when comes the time of SAP integration. This paper is dedicated to these people.
I have been installing many SAP R/3 on NT and SQL 7 for two years now, and fine tuned the platforms to satisfy the users. For these past years, I’ve seen extraordinary enhancements in the SAP R/3 integration in SQL Server. The release of SQL Server 7 was a major step in really amazing performances, for a tenth of the cost of an Oracle/Unix system.
A True Multi-Tier Architecture
SAP R/3 uses a multi-tier architecture, based on presentation clients, one or many
application servers and one database server. Depending on the number of concurrent users and on the size of the live database, the application server and the database server can be combined in one powerful box. Beginning with version 4, SAP can use an Internet Server to offer client a web Access. Though, you can have up to four layers : presentation, internet, application and database, depending if you need web access or not. The customer inside the company can use the standard three-tier system, and the customers or suppliers outside the company can use the four-tier system, accessing it through their web browser.
Integration and Administration
The installation of a SAP system should be generally managed by a SAP certified consultant or by a certified administrator within the company. The installation guide provided by SAP is well enough documented to run the set up on your own, but you should pay attention to use certified hardware (go to www.r3onnt.com to check your hardware). If you want to make sure your installation is correct, follow that simple guideline :
- Install SQL Server 7 on the database server. Use Code Page 850 and the binary sort order (required by SAP).
- Size tempdb at least at 250 MB.
- Check the paging file on the application server and make sure it is at least three time the size of your available RAM. If not, your SAP system won’t start or will generate a Dr Watson.
- Check the Virtual Memory Manager of your application and database servers to make sure you maximize througput for network applications (Control Panel, Network, Server service).
- Disable any screen saver (especially the OpenGL ones) on both servers.
- Install SAP R/3. Modify the size and the location of files if necessary. It is better to right size the file from the beginning and avoid leaving SQL doing the job through autogrow. Make sure your SAP database log file is on a physical disk different from the database data files.
- Create at least two Update process and many Batch processes for day and night operations depending on your system load. The limitation of one update process in SQL 6.5 was really a performance breaker, but with 7.0, this limitation no longer exists.
- Backup your system once it’s installed.
If you are using SAP R/3 4.5 or 4.6, you will have the opportunity to manage your SAP and SQL system with the MMC, since SAP ships a snap-in for the version 4.5 and 4.6. With former releases, you will use the MMC for SQL Server 7 and the SAPGUI for R/3.
The SAP R/3 snap-in relies on the SQL-NS (Name Space) to offer the adminstrator the full functionnalities of the SQL Server snap-in. So from one snap-in you have access to the standard windows-based SAPGUI interface for SAP operations and to the classical SQL Server wizards and dialog boxes for SQL Server operations. The whole administration can also been handeld through a web-based interface, looking like the MMC.
The setup process normally tune your SQL installation. Crosschecking what it does is not an option.
Tuning SQL Server 7
When comes the time of tuning a SQL Server 7 system, many administrators think everything is automatic. It’s definitely simpler than a SQL 6.5 system, but not as automatic as you can imagine.
Memory
If you are using a dedicated database server, do not touch anything and leave SQL Server adjusting the necessary memory. If you are mixing application and database, you must use a fixed memory size of 45 % (min and max) with a central instance (everything on the same server), and 40 % min and 65 % max if you are using just an update instance on the database server (you have then two application servers, one shared with the database).
In all the case, always configure SQL Server to reserve physical memory.
Check the value of the index create memory option is 16000.
Network
Change the network packet size to 8,192 bytes. This value has been tested by SAP as being the optimized one.
Processor
On a dedicated database SMP server, boost the SQL Server priority on Windows NT. I’ve encountered some performance problem with this option enabled. So monitor your system and checked SQL is not starving the system. If you encounter SQL Server error 17824, though it is a network error, Microsoft has confirmed that could be a priority boost side effect.
If you CPU load is close to 100%, monitor the context switching performance monitor counters. If you encounter excessive context switching, try to enable the lightweigh pooling option. It may reduce the CPU load a little bit. But I’ve side effect of this option if you are using self developped extended stored procedure. So pay attention to what you are doing.
Conclusion
I only gave you there tips and tricks to tune your SAP-SQL Server system. But fine tuning is not reduced to modifying the value of some parameters. I will discuss in other articles hardware choice, hardware/software tuning, designing efficient disk arrays for OLTP systems with SQL Server.
But if you are in charge of a SAP-SQL Server system, download and read carefully the white paper Microsoft SQL Server 7 Performance Tuning Guide at http://msdn.microsoft.com/library/techart/msdn_sql7perftune.htm to find all the necessary information for tuning a SQL Server 7 system and the white paper SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7 at http://msdn.microsoft.com/library/techart/sql7sapr3.htm.
