SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability35
Multi-instance support for SAP components
SAP products support multiple instances of SQL Server 2005 on the same server. However, running the database of the SAP production system together with other application databases on one server is not recommended. Multiple-instance SAP configurations are typically used to run test, development, and demo systems on one server.
An alternative is to run two or three SAP databases on one SQL Server 2005 instance. The advantage of having multiple SAP databases handled by one SQL Server 2005 instance is easy administration. The trade-off is that different SAP systems must share the resources of one SQL Server 2005 instance, including data cache, processors, and tempdb. The resources used by each instance can be restricted, although adding this restriction entails more administration overhead.
SAP database configuration
This section describes the settings that are used by a SQL Server 2005 instance running a SAP database. It also discusses the special settings for SAP with the SQL Server 2005 lock manager.
Most of the SQL Server 2005 parameters described in this section are set correctly when the SAP software is installed. These installation settings cover most needs, even on high-end systems. These parameter settings are typically changed only occasionally, such as when there is a need to improve performance.
Note that most of the tuning and balancing on memory allocations, I/O, and so on within the SQL Server instance is automated.
SQL Server configuration for SAP
SQL Server 2005 contains approximately 50 instance global configuration options that are usually accessed using the sp_configure stored procedure. These configuration options can increase the flexibility and uptime of SQL Server 2005. Typically, only a small number of these configuration options require that a change be made to run SAP products proficiently.
This section describes the options that are commonly used for tuning the SAP workload.
Microsoft Corporation © 2005