SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability33
Setting autogrowth. Avoid situations where automatic growth is set by manually increasing the size of the data files proportionally in advance. Although the data files can be manually manipulated proactively, leave autogrowth on as a safety measure in case of an emergency such as when the database runs full.
SAP and SQL Server file groups
SQL Server 2005 allows for the creation of file groups to place individual tables in dedicated locations. SQL Server file groups are similar to Oracle® tablespaces.
SQL Server 2005 collects the SAP data and each of the SAP data files in the default file group, unless otherwise specified in the CREATE TABLE statement. See in this paper for more information.
File groups require complex administrative overhead. Therefore, SAP does not support SQL Server file groups. Using file groups in SAP databases can lead to errors in SAP release upgrades or failures when applying support packages because SAP products cannot create objects in file groups.
Number of log files for SAP databases
The number of log files is chosen during SAP product installation. Because the SQL Server 2005 transaction log is written sequentially, typically only one transaction log file is required. Considerations for defining the number and size of log files include:
Using multiple log files. Having multiple physical log files on different partitions does not improve performance. Multiple log files are generally used on an exception basis only when there is a lack of space on one partition. SQL Server 2005 cannot write to multiple log files in parallel. To ensure that the transaction log file does not get lost in a hardware failure, duplicate the log in storage by using at least RAID 1.
Setting the physical log file size. It is important to create a physical log file that is of a sufficient size, even if the log file is set to autogrow. The SAP installation program typically uses a default log file size of 1 GB. For a SAP installation, set the initial size of the transaction log file to 5 GB. In most high-end SAP systems, the size of the transaction log file ranges from 10 to 20 GB.
Setting the size of virtual log files. Internally, the physical log file is administered by virtual log files. The size and number of virtual log files depends on the size of the physical log file, plus the growth portion. For performance reasons, it is better to have fewer larger virtual log files, instead of a large number of small virtual log files. During installation, SAP initially configures the growth factor of the log file to 50 percent, which is recommended.
Microsoft Corporation © 2005