Microsoft SQL Server Standby Database

You can protect the Microsoft SQL Server against failure by setting up a standby database. The standby database can come online in the event of primary database failure. The standby contains an up-to-date copy of the primary database and runs in standby mode. The copy is set up by an initial restore of the primary database to the standby database followed by periodic shipping of backed-up transaction logs from the primary to the standby database, known as “log shipping” .

For more information about log shipping, see SAP Note 493290 and the SQL Server documentation.

The main advantage of a standby database is to prevent data loss.

For more information about how you can combine the Microsoft SQL Server standby database with Database Mirroring , or the Windows Server Failover Clustering , see Comprehensive Microsoft SQL Server High Availability Solution .

• You can use the standby database for read-only purposes by noncritical applications such as database consistency checks. This means that it must always be left in a logically consistent state after log shipping. Therefore, you must specify an undo file on the standby database, to allow rollbacks of uncommitted transactions contained in the transaction log from the primary database.

• More frequent log shipping means:

  • Higher processing workload

  • Reduced time to bring the standby database online after a failure on the primary database

  • More up-to-date standby database, so there is less data loss following failure that destroys the current transaction log on the primary database

• A single SQL Server database instance can act as backup for several production databases, as it is unlikely that all production databases fail at the same time (especially if they are on geographically separate sites).

• Log shipping can be implemented using buffering. This backs up the transaction logs regularly (such as every 10 minutes), which are then held for a longer period (such as two hours) before being applied.

1. Initially, you back up the primary database and restore it to the standby database.

2. Periodically, the system automatically performs log shipping.

3. If the primary database fails, you:

   1. Determine the cause of the problem

   2. In view of your diagnosis, decide which transaction logs from the time of failure to apply to the standby database

   3. If required and if possible, back up the current transaction log from the primary database

   4. Ship transaction logs to the standby database as required

   5. Apply transaction logs to the standby database, if required

   6. Recover the standby database

   7. Switch to the standby database, which is now the production database

http://www.sdn.sap.com/irj/sdn/ha