To set up, maintain, and bring online a standby serverA standby (warm backup) server is a second server that can be brought online in the event of failure of the primary production server. The standby server contains a copy of the databases on the primary server, possibly including the system databases if this is a complete duplicate of the primary system. This copy is maintained by initially backing up the databases on the primary and restoring them on the standby. 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™ Failover Support. 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 disk, is shared by the primary and secondary physical servers that underlie the virtual server.
Important All user processes need to 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. Unless the primary server is taken off the network or renamed manually, and the standby server renamed, the standby server will have a different network name and address than the server the users were using previously. To make use of failover support whereby network names and addresses are maintained automatically, use Microsoft Windows NT® Clustering Service with SQL Server.
Using a standby server allows users to continue working with databases despite the primary server becoming unavailable. When the primary server becomes available again, any changes to the standby server’s copies of databases need to be restored back to the primary server. Otherwise, those changes are lost when users start using the primary server again, and the primary server's databases are backed up and restored on the standby server again.
Implementing a standby server involves three phases:
On the primary server:
The frequency of transaction log backups created on the primary server depends on the volume of transaction changes of the primary production server database. If the transaction frequency in the primary database 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 To implement a standby server with a copy of master from a production server, it is not possible to back up the transaction log of master. Only a database backup and restore of master is possible.
A standby server is set up and maintained as follows:
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 transactions that were rolled back are modified, undoing 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 need to 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.
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, for example.
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. It is possible to synchronize the primary databases with the standby copies and bring the standby server online, before the users use the standby copies, by:
A standby server can also contain backups of databases from several servers running 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 could be used. The database backups from the five primary systems could be loaded onto the single backup system, reducing the number of resources needed and saving money. It is unlikely that more than one primary system would fail at the same time. Additionally, the standby server could be of higher specification than the primary servers to cover the remote chance that more than one primary system is unavailable at a single given time.
Note 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.
To set up, maintain, and bring online a standby server
| Restoring a Database to a Prior State | SQL Server Failover Support |
| RESTORE |