Using Standby Servers
A standby server is a second server that can be brought online if the primary production server fails. The standby server contains a copy of the databases on the primary server. A standby server can also be used when a primary server becomes unavailable due to scheduled maintenance. For example, if the primary server needs a hardware or software upgrade, the standby server can be used.
A standby server allows users to continue working with databases if the primary server becomes unavailable. When the primary server becomes available again, any changes to the standby server's copies of databases must be restored back to the primary server. Otherwise, those changes are lost. When users start using the primary server again, its databases should be backed up and restored on the standby server again.
Implementing a standby server involves these phases:
- Creating the database and ongoing transaction log backups on the primary server.
- Setting up and maintaining the standby server by backing up the database on the primary server and restoring them on the standby server.
- Bringing the standby server online if the primary server fails.
Important All user processes must log in to the standby server and restart any tasks they were performing when the primary server became unavailable. User processes are not switched automatically to the standby server and transactions are not maintained between the primary server and the standby server. If the primary server is taken off the network or renamed manually, and the standby server is renamed, then the standby server will have a network name and address different from the server the users were using previously.
Periodically, transaction log backups from the databases on the primary server are applied on the standby to ensure that the standby remains synchronized with the primary server. In the event of the primary server failing, or even if just a single database fails, the databases on the standby server are made available to user processes. Any user processes that cannot access the primary server should use the standby server instead.
A standby server configuration is not the same as the virtual server configuration used in Microsoft® SQL Server™ 2000 failover clustering. A standby server contains a second copy of the SQL Server databases. In a virtual server configuration, a single copy of the databases, loaded on a shared cluster disk, is shared by the primary and secondary physical servers that underlie the virtual server.
Creating the Backups on the Primary Server
On the primary server:
- Create a full database backup of each database to be duplicated. For more information, see Database Backups.
- Periodically, create a transaction log backup of each database to be duplicated. For more information, see Transaction Log Backups.
The frequency of transaction log backups created on the primary server depends on the volume of transaction changes of the production server database. If the transaction frequency is high, it may be useful to back up the transaction log frequently to minimize the potential loss of data in the event of failure.
Important When restoring a copy of master from a production server to a standby server, you cannot back up the transaction log of master. Only a database backup and restore of master is possible.
Setting Up and Maintaining the Standby Server
A standby server is set up and maintained as follows:
- Restore the database backups from the primary server onto the standby server in standby mode, specifying an undo file (one undo file per database).
When a database or transaction log is restored in standby mode, recovery needs to roll back any uncommitted transactions so that the database can be left in a logically consistent state and used, if necessary, for read-only purposes. Pages in the database affected by the uncommitted, rolled back transactions are modified. This undoes the changes originally performed by the uncommitted transactions. The undo file is used to save the contents of these pages before recovery modifies them to prevent the changes performed by the uncommitted transactions from being lost. Before a subsequent transaction log backup is next applied to the database, the uncommitted transactions that were previously rolled back by recovery must be reapplied first. The saved changes in the undo file are reapplied to the database, and then the next transaction log is applied.
Note There must be enough disk space for the undo file to grow so that it can contain all the distinct pages from the database that were modified by rolling back uncommitted transactions.
- Periodically, apply each subsequent transaction log, created on the primary server, to the databases on the standby server. Apply each transaction log in standby mode, specifying the same undo file used when previously restoring the database.
The frequency of transaction log backups applied to the standby server depends on the frequency of transaction log backups of the primary production server database. Frequently applying the transaction log reduces the work required to bring the standby server online in the event of a production system failure.
In standby mode, the database is available for read-only operations, such as database queries that do not attempt to modify the database. This allows the database to be used for decision-support queries or DBCC checks.
Bringing the Standby Server Online
When the primary server initially becomes unavailable, it is unlikely that all the databases on the standby server are in complete synchronization. Some transaction log backups created on the primary server may not have been applied to the standby server yet. Additionally, some changes to the databases on the primary server are likely to have occurred since the transaction log on those databases were last backed up, especially in heavily used systems. Before the users use the standby copies, it is possible to synchronize the primary databases with the standby copies and bring the standby server online by:
- Applying to the standby server in sequence any transaction log backups created on the primary server that have not yet been applied.
- Creating a backup of the active transaction log on the primary server and applying the backup to the database on the standby server. The backup of the active transaction log when applied to the standby server allows users to work with an exact copy of the primary database as it was immediately prior to failure (although any noncommitted transactions will have been permanently lost). For more information, see Transaction Log Backups.
If the primary server is undamaged, as in the case of planned maintenance or upgrades, you can back up the active transaction log with NORECOVERY. This will leave the database in the restoring state and allow you to update the primary server with transaction log backups from the secondary server. Then you can switch back to the primary server without creating a complete database backup of the secondary server. For more information, see BACKUP.
- Recover the databases on the standby server. This recovers the databases without creating an undo file, making the database available for users to modify.
A standby server can contain backups of databases from several instances of SQL Server. For example, a department could have five servers, each running a mission-critical database system. Rather than having five separate standby servers, a single standby server can be used. The database backups from the five primary systems can be loaded onto the single backup system, reducing the number of resources required and saving money. It is unlikely that more than one primary system would fail at the same time. Additionally, the standby server can be of higher specification than the primary servers to cover the remote chance that more than one primary system is unavailable at a given time.
To set up, maintain, and bring online a standby server
SQL-DMO