SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability45
In addition, use the following Transact-SQL command to verify that these settings were performed:
Select INDEXPROPERTY(object_id(‘<table_name>’), ‘<Index_name>’, ‘IsPageLockAllowed’)
SQL Server 2005 Data-Availability Features
SQL Server 2005 data availability features take full advantage of the data on the primary image and on one computing node. It is a best practice to exploit the SQL Server 2005 data availability features before considering high availability architectures to guarantee availability and improve reliability. For more information, see earlier in this paper.
Backup and recovery improvements
mySAP can take advantage of new SQL Server 2005 backup capabilities, including fine-grained online repairs, online backups, and redo controlled by checkpoint to speed up recovery. Unlike other databases, a special backup tool is not required when using SAP with SQL Server 2005.
In SQL Server 2005, online backups perform only one data backup at a time for each database. The backup and restore functionality includes full-text catalogs.
In addition, fine-grained online repairs can be performed so the database is available while single pages or file groups are restored. Fine-grained online repairs allow the database administrator to work with the system at a granular level, leaving only part of the system offline such as to check and restore data at the page-level.
Online backups allow transaction log backups to be performed at the same time. That is, database files and file groups and the log can be backed up concurrently.
Transaction log backup cycling. With typical SAP customers, the cycling of transaction log backups during online backup performs well, especially in scenarios where large batch schedules are run during the night. This increases the safety level on the customer side and the ability to restore to a point in time, even to the timeframe during which the online backup ran.
Single page restoration with the database online. SQL Server 2005 can restore single pages from online and transaction log backups while the rest of the database remains online. This is an advantage in common support cases, including cases where the page corruption of one or a few pages results in the destruction of data. In such cases, a SAP customer does not need to restore terabytes of data to get the database physically consistent again. Instead, a customer can restore the few corrupt pages while the rest of the data remains accessible.
Recovery Phase. In SQL Server 2005, a database in the recovery phase becomes available after all of the transactions not covered by the last checkpoint are redone. The recovery phase consists of two main phases that change data on the database—the redo phase and the undo phase.
Microsoft Corporation © 2005