Microsoft SQL Server Standby Database

Use

The Microsoft SQL Server can be protected against failure by setting up a standby database. The standby database can be brought 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 on log shipping, see SAP Note 493290 and the SQL Server documentation.

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

Integration

For more information on how you can combine Microsoft SQL Server Standby Database with the Microsoft Cluster Server on Windows, see Comprehensive Microsoft SQL Server High Availability Solution.

Features

·        The standby database can be used for read-only purposes by non-critical applications such as database consistency checks. This means that it must always be left in a logically consistent state after log shipping. Therefore, an undo file must be specified 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 in which the current transaction log on the primary database is destroyed

·        A single standby database 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 means that the transaction logs are backed up regularly (such as every 10 minutes) then held for a longer period (such as two hours).

Activities

...

       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:

...

                            a.      Determine the cause of the problem

                            b.      In view of your diagnosis, decide which transaction logs from the time of failure should be applied to the standby database

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

                            d.      Ship transaction logs to the standby database as required

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

See also:

You can find more information on SAP Service Marketplace at:

service.sap.com/ha