3 4
A standby server can be maintained to take the place of the primary production server, if needed. In this lesson, you will learn about setting up and maintaining a standby server. This includes learning how to automate the maintenance of a standby server. You will also learn how to bring a standby server online, and how to bring the primary server back online.
A standby server is a secondary SQL Server installation that is kept up-to-date with a primary SQL Server installation through the application of transaction log backup files. By using standby servers, a database administrator can minimize the length of time that users are unable to access one or more production database(s) in case of server failure or the need for server maintenance. The databases on a standby server can also be used as reporting servers to offload reporting and analysis tasks.
A standby server must maintain an exact copy of one or more production databases on the primary server. This is initially accomplished through the use of a full database backup on the primary server that is restored to the standby server using either the NORECOVERY or the STANDBY restoration options. Using one of these two options leaves the standby database in recovery mode, so that additional transaction logs can be applied to it.
Next, to maintain an exact copy on the standby server, regular transaction log backups are performed on the primary server and applied to the standby server (again leaving the standby server in recovery mode). The frequency with which transaction log backups are performed on the primary server and applied to the standby server determines the amount of work (and length of time) required to bring the standby server up-to-date and online in the event the standby server must be promoted.
To promote the standby server to become the new primary server, all unapplied transaction log backup files must be applied to the standby server. In addition, the active portion of the transaction log on the primary server must be backed up and applied to the standby server. This final restoration is performed using the RECOVERY option. Users can then use the database on the standby server, which contains all data from the primary server exactly as it was prior to its failure (other than uncommitted transactions, which are permanently lost).
Note
The active portion of the transaction log on the primary server can be backed up using the NORECOVERY backup option. This option leaves the primary database in recovery mode. When the primary server is ready to be brought back online, the transaction logs from the standby server (for the period that users were using the standby server as the primary server) can be applied to the primary server. This avoids the necessity of applying a complete database backup and all applicable transaction logs to restore the primary server. The result is a significant decrease in the time required to bring the primary server back online.
When the standby server is brought online for use in place of the primary server, either the standby server must be renamed using the name of the primary server or user processes must know to connect to the standby server (using the name of a standby server) rather than the primary server. All uncommitted transactions must be restarted. Uncommitted transactions cannot be maintained between the primary server and the standby server.
Note
User logins must be created on the standby server prior to bringing the standby server online as the new primary server. This is generally accomplished using one of the following methods.
If the standby server is only providing services to a single production server, you might want to create the logins on the standby server when the standby server is created. However, if the standby server is providing services to multiple databases from multiple instances of SQL Server, generally you will not create logins on the standby server until it is brought online as the primary server for a particular database.
You can automate the process of maintaining a standby server by creating backup, copy, and restore jobs that are periodically executed by SQL Server Agent on the primary server and on the standby server. This automated process is called log shipping. You can also designate a third server to monitor the execution of the log shipping jobs on the primary server and on the standby server; the third server is frequently used to monitor other pairs of log shipping servers as well.
Note
You can create these jobs using the Database Maintenance Plan Wizard in SQL Server Enterprise Manager. You must be a member of the sysadmin server role to run the Database Maintenance Plan Wizard. To start the Database Maintenance Plan Wizard, click Database Maintenance Planner on the Tools menu, or right-click the Database Maintenance Plan container (in the Management container) and then click New Maintenance Plan. (The use of most of the Database Maintenance Planner features was covered in Chapter 13.)
In the Select Servers page, select the primary server. See Figure 16.1.
Figure 16.1
Selecting a primary server for log shipping.
Next, in the Select Databases page, select the database for which you want to configure log shipping, and specify log shipping by selecting the Ship The Transaction Logs To Other SQL Servers (Log Shipping) check box. You can select only one database at a time. See Figure 16.2.
Next, advance to the Specify the Database Backup Plan and clear the Back Up The Database As Part Of The Maintenance Plan check box. See Figure 16.3.
Figure 16.2
Selecting a database for log shipping.
Figure 16.3
Clearing the full database back up check box.
Note
Next, in the Specify Transaction Log Backup Disk Directory page, specify the directory into which the transaction log backup files will be stored. Make sure this location has sufficient space to hold the shipped logs, or log shipping will fail. See Figure 16.4.
Figure 16.4
Selecting a storage location for transaction log backups.
Next, in the Specify The Transaction Log Share page, specify the network share for the storage location for the transaction log backup files. See Figure 16.5.
Figure 16.5
Specifying the network share for the transaction log backup files.
You must create this network share for the directory in which the transaction log backup files are stored. The domain user account used by the SQL Server Agent service on the primary server must have read and write access to this network share, and the domain user account used by the SQL Server Agent service on the standby server must have read access to this network share.
Next, in the Specify The Log Shipping Destinations page, click the Add button to display the Add Destination Database dialog box, where you specify the standby server and configure its properties. The standby server is called the destination server in the wizard. In addition to using log shipping to create and maintain standby servers, you can also use log shipping to create read-only copies of one or more production server databases rather than using replication. Figure 16.6 illustrates the Specify The Log Shipping Destinations page after a log shipping destination has been specified.
Figure 16.6
Specifying the standby server.
In the Add Destination Database dialog box, there are a number of properties you can configure for the destination database. See Figure 16.7.
Figure 16.7
Specifying the properties of the destination database.
In the Destination Database group box, you can choose to specify a different database name for the destination database. This is useful for creating a destination read-only database for reporting purposes. If you want the destination database to be viewable for read-only queries, you must select the Standby Mode option button in the Database Load State group box. The default is the No Recovery Mode option button. If you choose to update and overwrite an existing database (rather than creating a new database), you should select the Terminate Users In Database (Recommended) check box to automatically terminate all users in the existing database. If you want to use the destination database as a standby server that may need to assume the role of the primary server, you must select the Allow Database To Assume Primary Role check box to specify that it can assume the primary role if necessary. When you select this check box, you must specify the directory, in the Directory text box, for storing transaction log backups during the period the standby server is functioning as the primary server.
Next, in the Initialize The Destination Databases page, you specify the backup file containing a full database backup to be used to initialize the destination database on the standby server (see Figure 16.8).
Figure 16.8
Specifying the backup file for initialization.
You can choose to perform a full backup immediately by selecting the Perform A Full Database Backup Now option button, or you can select the Use Most Recent Backup File option button to specify an existing recent backup file. If you specify a recent backup file, you must include the path and filename of the existing backup file in the Use Most Recent Backup File text box. This backup file will be placed in the log shipping share so that it is available to the standby server for initialization of the destination database.
Next, in the Log Shipping Schedules page, specify the log shipping schedule. See Figure 16.9.
In the Backup Schedule group box, the default frequency for backing up the transaction log on the primary server is 15 minutes. Click the Change button to display the Edit Recurring Job Schedule dialog box to modify this frequency. Next, in the Copy/Load Frequency spin box, specify the frequency with which the transaction log backup files are copied to the standby server and restored (the default is 15 minutes). In the Load Delay spin box, you can specify a delay between the copy and the load (restore) of the transaction log backup files (the default is no delay). Finally, in the File Retention Period spin box, you can specify the length of time the transaction log backup files are retained on the log shipping share (the default is 24 hours). If the standby server is unable to copy these files before the retention period expires, the destination database on the standby server will have to be reinitialized. The frequence of the transaction log backups and the length of the file retention period will affect the amount of disk space that the transaction log backup files will require.
Next, in the Log Shipping Thresholds page, specify the log shipping alert thresholds. See Figure 16.10.
Figure 16.9
Specifying the log shipping schedule.
Figure 16.10
Specifying the log shipping alert thresholds.
The Backup Alert Threshold alert defines the maximum length of time between transaction log backups on the primary server (the default is 45 minutes or three times the interval between transaction log backups). If the defined length of time is exceeded, an alert will fire to notify an administrator of a problem with the automated log shipping process. The Out Of Sync Alert Threshold alert defines the maximum length of time between the most recent transaction log backup on the primary server and the restore of the transaction log backup to the standby server (the default is 45 minutes or three times the interval between transaction log restores).
Next, in the Specify The Log Shipping Monitor Server Information page, specify a log shipping monitor for monitoring log shipping jobs on the primary server and the standby server. See Figure 16.11.
Figure 16.11
Specifying a central monitoring server.
This should generally be a server other than the primary or the standby server. The monitoring server can provide monitoring services to multiple log shipping pairs of servers. You must specify the authentication mode the SQL Server Agent service will use to connect to the monitoring server. Select either Windows authentication or SQL Server authentication. If SQL Server authentication is selected, you must use the SQL Server login account, log_shipping_monitor_probe.
The next two pages, the Reports to Generate page and the Maintenance Plan History page, were covered in Chapter 13. See Figures 13.48 and 13.49.
When you are finished configuring your maintenance plan, the Database Maintenance Plan Wizard Summary page appears. See Figure 16.12. Verify that the plan information is correct, and then click Next. Finally, click the Finish button in the Completing the Database Maintenance Plan Wizard page, as shown in Figure 16.13.
After the Database Maintenance Plan Wizard completes its task, the database selected for log shipping is restored in recovery mode (using either the NORECOVERY or STANDBY options) on the standby server. See Figure 16.14.
Figure 16.12
Database Maintenance Plan Summary page.
Figure 16.13
Completing the Database Maintenance Plan Wizard page.
Figure 16.14
Standby server with database in standby mode.
In this practice you use the Database Maintenance Planner Wizard in SQL Server Enterprise Manager to automate log shipping.
To create a standby server
The Attach Database - SelfPacedCPU dialog box appears.
The Browse for Existing File - SelfPacedCPU dialog box appears.
Notice that the LogShipDB database is about to be attached.
A SQL Server Enterprise Manager message box appears to inform you that attachment of the database has completed successfully.
Notice that the LogShipDB database appears in the list of databases.
The Welcome To The Database Maintenance Plan Wizard page appears.
The Select Servers page appears.
The Select Databases page appears with the LogShipDB check box selected.
The Update Data Optimization Information page appears.
The Database Integrity Check page appears.
The Specify The Database Backup Plan page appears.
The Specify Transaction Log Backup Disk Directory page appears.
The Specify The Transaction Log Share page appears.
The Backup Properties dialog box appears.
The Backup Properties dialog box appears.
The wizard verifies that the specified share exists and the Specify The Log Shipping Destinations page appears.
The Add Destination Database dialog box appears.
The wizard verifies that the specified share exists and the Specify The Log Shipping Destinations page reappears displaying the destination server and database.
The Initialize The Destination Databases page appears.
The Log Shipping Schedules page appears.
The Edit Recurring Job Schedule dialog box appears. Note that there are two spin boxes, one for changing the number and one for specifying the increment of time.
The Log Shipping Schedules page reappears.
The Log Shipping Thresholds page appears. Notice that the Backup Alert Threshold is set to 5 minutes and the Out Of Sync Alert Threshold is set to 3 minutes.
The Specify The Log Shipping Monitor Server Information page appears.
The Reports To Generate page appears.
The Maintenance Plan History page appears.
The Database Maintenance Plan Wizard Summary page appears.
The Completing The Database Maintenance Plan Wizard page appears.
Notice the progress of the steps the wizard is performing.
In the Databases container for the SelfPacedCPU\MyNamedInstance, notice that the LogShipDB database container appears containing a read-only copy of the LogShipDB database. If it does not appear, right-click the Databases container and then click Refresh.
To monitor the status of log shipping on the monitor server, expand the Management container of that server and then click Log Shipping Monitor (if you have just configured log shipping, right-click the Management container and then click Refresh). The status of all log shipping servers is displayed in the details pane. See Figure 16.15.
Figure 16.15
Log shipping monitor.
The log shipping monitor displays the date and time of the most recent transaction log backup on the primary server in the Last Backup column, the most recent copy of the transaction log backup file to the network share in the Last Copy column, the most recent restore of the transaction log backup file to the standby server in the Last Restore column, and the synchronization status in the Status column. Click the Show/Hide Console Tree/Favorites button on the toolbar to display only the details pane.
To view the backup history for the primary server, right-click the log shipping pair and then click View Backup History to display the Database Maintenance Plan History dialog box. See Figure 16.16.
Figure 16.16
Viewing the backup history for the primary server from the monitor server.
To view the copy and restore history for the standby server, right-click the log shipping pair and then click View Copy/Restore History to display the Secondary Server Log Shipping History dialog box. See Figure 16.17.
To modify the properties of log shipping, right-click the log shipping pair and then click Properties to display the Log Shipping Pair Properties dialog box. Log shipping information is displayed on this page, including the last backup file, the last file copied, the last file loaded, and the times and deltas for each. See Figure 16.18.
Click the Source tab to modify alert properties for the primary server (see Figure 16.19). Click the View Backup Schedule button to view the backup schedule.
Click the Destination tab to modify the alert properties for the standby server (see Figure 16.20). Click the View Copy Schedule button to view how often database copies are made, or click the View Load Schedule button to view how often the database is restored.
Figure 16.17
Viewing the copy/restore history for the standby server from the monitor server.
Figure 16.18
Viewing log shipping pair properties.
Figure 16.19
Viewing the Source tab of log shipping pair properties.
Figure 16.20
Viewing the Destination tab of log shipping pair properties.
In this practice you use the Log Shipping Monitor in SQL Server Enterprise Manager to monitor log shipping.
To monitor log shipping
The current status of the log shipping pair is displayed in the details pane.
Notice that the window now displays only the contents of the details pane (for easier viewing of all columns).
The Database Maintenance Plan History - SelfPacedCPU dialog box appears displaying the history of the Log Shipping maintenance plan. Notice that the transaction log is being backed up every minute and each successful job has a blue check mark in the status column.
The Secondary Server Log Shipping History - SelfPacedCPU\MyNamedInstance dialog box appears. Notice that the copy job and the load job occur every minute. The load job restores all transaction log backup files that have been copied, and then the copy job connects to the primary server and copies any new transaction log backup files to the standby server.
The Log Shipping Pair Properties dialog box appears. Notice that Load Delta indicates that the synchronization delay between the primary database and the standby database is one minute (it will be two minutes on some slower computers).
SQL Query Analyzer appears displaying a connection to the SelfPacedCPU default instance.
The Open Query File dialog box appears.
Notice that this script changes the ContactTitle field for one of the customers in the LogShipDB database.
Notice that the ContactTitle field for CustomerID ANATR is changed.
To bring a standby server online, you must run the following system stored procedures in order.
Note
In addition, you must verify that the new primary server contains all required logins. You can use a saved Transact-SQL script, or if you used the DTS Transfer Logins Task to create a Bcp output file containing these logins, you can use the sp_resolve_logins system stored procedure to input these logins from the Bcp output file.
After completion of these steps, the standby server is ready to function as the new primary server. The former primary server is no longer part of a shipping pair. You must add the original primary server or another server as a standby server to re-establish a shipping pair. To bring the original primary server back online, repeat the three-step process just described.
In this practice you use stored procedures to bring a standby server online as the primary server and deactivate the primary server and its log shipping jobs.
To bring a standby server online as the primary server
The Open Query File dialog box appears.
This script runs the sp_change_primary_role system stored procedure, which backs up the current transaction log for the LogShipDB database and sets the database to read-only.
The script executes and displays a message regarding the backup of the current transaction log.
Note
The current jobs are displayed in the details pane.
The Connect To SQL Server dialog box appears.
The Open Query File dialog box appears.
This script runs the sp_change_secondary_role system stored procedure, which copies the current transaction log from the primary server and restores it to the LogShipDB database on the standby server. It also restores the database to multiuser mode and removes the read-only setting.
Notice that the script copies the current transaction log (and any other logs it has not previously copied) and applies it to the database on the standby server. It deletes the copy and load jobs that were running on the former standby server and enables the transaction log backup job on the new primary server. If the second result set indicates that exclusive control of the database could not be obtained to perform the restore task, rerun the system stored procedure.
The Open Query File dialog box appears.
This script runs the sp_change_monitor_role system stored procedure, updating the monitor server regarding the change in the role of the primary and standby server. It also specifies the storage location for the transaction log backup files being created on the new primary (former standby) server.
Notice that the ContactTitle change was captured in the transaction log and applied to the new primary server before it was restored.
Notice that the query executes successfully. The new primary database is functioning successfully. If you receive an error stating that BEGIN TRANSACTION could not run because the database is read-only, you are connected to the default instance rather than to the named instance.
Using a standby server enables the database administrator to quickly bring a server online in case of a server failure or to perform necessary maintenance on a primary server. Log shipping automates this process using jobs and alerts administered by SQL Server Agent service on the primary server and on the standby server. A monitoring server is used to monitor log shipping pairs of servers. Bringing a standby server online as the primary server requires the execution of three system stored procedures. In addition, the database administrator must ensure that the new primary server has the necessary logins for the databases being promoted. This can be accomplished using the DTS Transfer Logins Task or using Transact-SQL scripts.