Lesson 4: Using a Standby SQL Server | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

If your business environment requires that the production server always be accessible, you may want to consider using a standby SQL Server. This lesson describes how to set up and use a standby SQL Server as well as how to restore the production server once the problem has been resolved. This lesson also introduces the use of clustering in the Enterprise Edition of SQL Server.

After this lesson, you will be able to

Set up a standby SQL Server computer and restore a production server

Estimated lesson time: 30 minutes

Setting Up a Standby SQL Server

If you determine that a standby SQL Server is appropriate for your business environment, you must decide on its purpose and then create and maintain it.

NOTE
In previous versions of SQL Server, a standby server was implemented by setting the no chkpt. on recovery option to true for databases on the standby server. This database option is no longer supported in SQL Server 7. The RECOVERY, NORECOVERY, and STANDBY options of the RESTORE statement now provide this functionality.

The Purpose of a Standby SQL Server

A standby SQL Server is a second server that mirrors the production server. You can use a standby SQL Server to replace a production server in case of a failure or to provide a read-only copy of one or more databases for decision support applications. In addition, a standby server can be used to validate database integrity by running the Database Consistency Checker (DBCC) without increasing the load on the primary server.

Initial Creation

Create a standby SQL Server by backing up the databases and transaction logs on the production server and then restoring them to another SQL Server, using the STANDBY option with the RESTORE statement.

When you restore backups to a standby SQL Server, consider the following facts and guidelines:

If the standby SQL Server is a duplicate of a production server, a copy of each database, including system databases, on the production server is restored to the standby SQL Server.

Use the MOVE_TO option to specify a new location for the database on the standby SQL Server if the location is different from the location on the primary server. For example, data files might be located in C:\Mssql7 on the primary server and in D:\Standby on the standby server.

You must specify the NORECOVERY or STANDBY option when restoring a backup to a standby server.

Do not recover the database until you replace the production server with the standby SQL Server.

TIP
An alternative to restoring the databases when initially creating them on the standby server is to copy database files from one server to another and then attach them to the standby server by using either the sp_attach_db or sp_attach_single_file_db system stored procedure.

The syntax for the RESTOREstatement is as follows:

RESTORE {DATABASE | LOG}{database_name | @database_name_var} FROM <backup_device> [, ...n]      [[,] MOVE 'logical_file_name' TO 'operating_system_file_name']  [WITH NORECOVERY | RECOVERY | STANDBY = undo_file_name}] ]

The STANDBY Option

The STANDBY option specifies the name of an undo file. This file contains the information used to define the state of the database before transactions in an unknown state are rolled back. This allows the database to be available for read-only operations before a recovery has been performed to fully recover the database.

Be aware of the following when specifying the undo file.

If the file specified by undo_file_name does not exist, SQL Server creates it.

The same undo_file_name can be used for each transaction log restore operation; SQL Server will delete and re-create it as necessary.

If the undo_file_name exists, SQL Server overwrites the file unless the file contains current undo information for another database.

The size of the undo_file_name is limited to the disk space available where the file resides.

The following example restores a database backup and a transaction log backup to a standby server. The database on the standby server is available in read-only mode, and additional transaction logs can be applied. This example assumes that the standby server and production server use the same locations for the database files.

USE MASTER RESTORE DATABASE nwcopy FROM nwcomplete WITH STANDBY = 'c:\mssql7\standby\nwundo.ldf' RESTORE LOG nwcopy FROM nwlogbackup WITH STANDBY = 'c:\mssql7\standby\nwundo.ldf'

Maintenance

To maintain the standby SQL Server, you must regularly restore all additional transaction log backups to it. Restoring transaction logs ensures that the standby SQL Server is consistent with the production server. Consider the following facts and guidelines when you maintain a standby SQL Server:

Perform regular transaction log backups on the production server.

Each time a transaction log backup is performed on the production server, restore it to the standby SQL Server.

Specify the NORECOVERY option when restoring backups to the standby server if you do not want the standby server to be available until it is brought online.

If you want the database to be available for read-only activity, you must specify the STANDBY option and supply a filename to contain the undo information.

If the standby SQL Server is used as a read-only database, change the dbo use only option to false in order to make the database available to users.

Using the Standby SQL Server as a Read-Only Server

The standby SQL Server can function as a read-only copy of the production server, reducing the amount of activity on the production server. Using the standby SQL Server in this way has the following additional advantages:

Users can access data for decision support queries in the time period between restore processes of each transaction log.

You can execute DBCC statements (for example, DBCC CHECKDB) to check the validity of the database.

Successfully restoring and validating each backup confirms the reliability of your backups.

Using the Standby SQL Server to Troubleshoot Data Corruption

Another use for a standby SQL Server is to determine the point at which a database became corrupted. You can use the STANDBY option to review the contents of the database for corruption or bad data as each transaction log backup is applied.

The following steps illustrate how to troubleshoot data corruption:

Apply a transaction log to a standby SQL Server by using the STANDBY option.

Check the consistency and examine the contents of the database before you apply the next transaction log by executing DBCC statements to check for corruption and using queries to check for bad data.

Continue applying the series of transaction logs and examining the contents of the database after each restore (steps 1 and 2) until you identify the cause of the problem and the time at which the problem occurred.

Restore the database and transaction logs on the production server by using point-in-time recovery to recover the database to the moment before the problem occurred.

Reset the standby SQL Server so that it reflects the state of the database on the production server.

Replacing a Production Server with a Standby SQL Server

You will typically use a standby SQL Server as a replacement for a production server to minimize down time. Follow these steps to bring a standby SQL Server on line to replace a production server:

Back up the transaction log from the production server. If possible, use the BACKUP LOG statement with the NO_TRUNCATE option for each database to back up any committed transactions since the last transaction log backup.

Take the production server off the network.

Change the computer name of the standby SQL Server to the name of the production server.

Restore the final transaction log to the standby SQL Server and specify the RECOVERY option. SQL Server recovers the database and allows users to read and write transactions to the database.

If the standby SQL Server has been used as a read-only server and you do not have additional transaction logs to restore, perform a manual recovery on the standby SQL Server by executing the RESTORE DATABASE WITH RECOVERY statement. At this point, the standby SQL Server is recognized as the production server.

Restoring the Production Server

You will typically restore the production server after the problem is resolved. To restore the production server, you must perform the following steps:

Perform complete database and transaction log (if needed) backups of the standby SQL Server to capture all changes. When you replaced the production server with the standby SQL Server, the standby SQL Server recorded changes in its copy of the database and transaction log.

Restore the standby SQL Server database and transaction logs to the production server.

Take the standby SQL Server off line.

Bring the production server on line.

Perform a complete database backup of all databases on the production server.

Restore the backups to the standby SQL Server, but do not recover the database. Use the STANDBY option, if appropriate. This ensures that the standby SQL Server is a duplicate of the production server and allows additional transaction logs to be restored.

Using Clustering

Clustering describes using multiple Windows NT Servers to provide increased reliability and capacity. Clustering is available in the Enterprise Edition of SQL Server running on Windows NT Enterprise Edition. In a clustered installation of SQL Server, clients connect to a virtual server, instead of connecting to an actual Windows NT Server as is done with a typical installation of SQL Server. Virtual servers appear externally to be regular Windows NT Servers when they are in fact a group of servers working together. The servers in a cluster monitor each other's status. If an application failure is due to a server failure, another server takes over. This process is known as failing over, or failover.

In a mission-critical environment where high availability of SQL Server databases is required and a Windows NT Server Enterprise Edition cluster is available, the Enterprise Edition of SQL Server installed with failover support provides the most scalable and reliable solution. For more information about clustering see Books online "Configuring SQL Server Failover Support"

Lesson Summary

You can use a standby SQL Server to replace a production server in case of a failure or to provide a read-only copy of one or more databases for decision support applications. In addition, a standby server can be used to validate database integrity by running the Database Consistency Checker (DBCC) without increasing the load on the primary server. The Enterprise Edition of SQL Server can be run on a Windows NT Server Enterprise Edition cluster.