SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability47
A database snapshot instantly creates a completely new set of data files that are used to store the original state of the pages at a point in time. No additional log file is created. A database snapshot does affect performance on the originating database because of a higher I/O load and a little more commutation time.
A database snapshot is extremely space efficient because it does not require a complete copy of the data. A database snapshot shares unchanged pages with the original database and only requires extra storage for the changed pages. After the snapshot is created and a page is changed for the first time, SQL Server 2005 copies the original page to the snapshot files (copy-on-write).
A database snapshot allows for the recovery of reduced availability from operator error by instantly creating persistent read-only copies. The snapshot appears to the outside as a read-only version of the original database, which was frozen at a point in time.
There are two basic scenarios for creating a snapshot:
Snapshot of the mirrored database. Create a snapshot on the mirrored database in a database-mirroring configuration to catch human errors that occur because of deleting or manipulating data. In this case, the snapshot of the mirrored database can be used for read-only purposes when the mirrored database is not accessible.
Mirror on the primary database. Create a mirror on the primary database when critical changes or imports are run and a failure or errors might entail complete restoration of the database. For example, applying SAP support packages is not a reversible operation, except by restoring the support packages to their earlier state, which entails a huge effort. In this case, a database snapshot can be used as a backup to revert the state of the database to the time when the snapshot was taken.
Multiple snapshots of a database can be created. However, as more snapshots are created, the I/O load increases. Note that a snapshot cannot be backed up. A clone of a snapshot cannot be created because a database snapshot does not permit background copying. In addition, the snapshot cannot be changed or attached to another server as a database to perform changes.
Online indexing is a new feature of SQL Server 2005 that allows index maintenance modifications to be performed online in OLTP systems such as SAP. Incremental reindexing with read consistent scans and lock handling improve SAP performance.
In SQL Server 2005, index creation can be performed in online or offline mode. In online mode, parallel change activity on the table is allowed. Index maintenance is performed offline by default with the table locked exclusively for this purpose.
With online indexing, SQL Server 2005 introduces a new parameter to the index DDL commands. For example, simple command syntax includes:
Create index [x~0] on x (a) with (online = on)
Drop index x.[x~0] with (online = on)
Microsoft Corporation © 2005