Setting Up a Hot Standby SQL Server

Setting up the hot standby server involves a series of tasks.

Consider the following points before scheduling restore operations on the standby server:

• Match the restore schedule set for the standby server to the backup schedule set for the production server. This synchronization ensures that the restored databases on the standby server are always up-to-date.

• To apply only the latest transaction logs backups since the last restore to a hot standby sever, you must select the Apply Log Backups Only check box. Selecting this check box skips any full backup job run since the last restore.

  For more information, see Restoring Transaction Logs on a Hot Standby Server.

• To restore all the backup jobs that ran after the last restore operation, ensure that the Apply Log Backups Only check box is cleared, and verify that the database already exists on the standby server.

• To perform a full restore of the database, ensure that the Apply Log Backups Only check box is cleared, and verify that the database does not exist on the standby server.

Before You Begin

Before setting up a hot standby server, and then getting it online in the event of a disaster, ensure that each of the following prerequisites are met:

• The production server and the hot standby server should be clients of the same CommServe.

• SQL Server Agent should be installed on both the production server and the hot standby server. Two licenses of the SQL Server Agent are required.

• The production server should be able to communicate with the hot standby server either using a local area network (LAN) within the same domain, a different domain, or across a Wide Area Network (WAN).

Procedure

The following is a high-level process for setting up a hot standby server:

1. Install the SQL Server Agent on the computer that you want to set up as a standby server.

2. Run a full backup of the SQL databases on the production server.

3. Run a restore of the last full backup that ran on the production server. Also, restore any differential or transaction log backups that ran after the last full backup was run. Each time, leave the database in a standby or a restoring state.

4. On the production server, schedule differential or transaction log backups of the SQL databases.

5. On the standby server, schedule restore operations of the latest backup copy of SQL databases, leaving the database in a standby or a restoring state.