Chapter 16: Maintaining High Availability
Download CD Content
About This Chapter
In previous chapters, you learned about maintaining the availability of your SQL Server databases by using RAID for storage of data and transaction log files; performing full, differential, and transaction log backups; and implementing replication. In environments that require the assurance of higher availability, such as Web-based solutions and 24x7 operations, additional methods for maintaining high availability are frequently implemented. In this chapter, you will learn about the function of standby servers and you will learn how to automate the maintenance of standby servers by using log shipping. Finally, you will be introduced to the use of SQL Server failover clusters.
Before You Begin
To complete this chapter, you must have
- A computer that meets or exceeds the minimum hardware requirements listed in Table 2.1, "Hardware Requirements," in Lesson 1 of Chapter 2.
- Microsoft Windows 2000 Server running on your computer on an NTFS partition.
- A computer with a NetBIOS name of SelfPacedCPU configured as a domain controller in the SelfPacedSQL.MSFT domain.
- Installed a default instance and at least one named instance of SQL Server 2000 (see Chapter 2).
- Failover clusters to achieve automatic failover to another server node in the event of server failure.
Lesson 1: Using Standby Servers
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.
After this lesson, you will be able to
- Set up a standby server
- Automate log shipping
- Monitor log shipping
- Bring a standby server online
- Bring a primary server back online
Estimated lesson time: 45 minutes
Using a Standby Server
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 / Using RAID for transaction log files is critical for ensuring that the active portion of the transaction log is available for backup and application to the standby server.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 / Use the SQL Server Setup program to rename a SQL Server 2000 installation after renaming the server using Windows 2000 or Windows NT 4.0.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.
- User logins from the primary server can be scripted and these scripts used to create the necessary logins on the standby server when needed.
- The DTS Transfer Logins Task in DTS Designer can be used within a job to automate the process of backing up, copying, and restoring the contents of the sysxlogins system table from the primary server to the standby server.
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.
Automating Log Shipping
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 / The log shipping feature is available only in the Enterprise, Enterprise Evaluation, and Developer Editions of SQL Server 2000.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 Plan Wizard 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.
Figure 16-2: Selecting a database for log shipping.
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-3: Clearing the full database backup check box.
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 DatabaseLoadState 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.
Figure 16-9: Specifying the log shipping schedule.
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 frequency 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-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.49 and 13.50.
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.
Figure 16-12: Database Maintenance Plan Summary page.
Figure 16-13: Completing the Database Maintenance Plan Wizard page.
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-14: Standby server with database in standby mode.
Practice: Creating a Standby Server
In this practice you use the Database Maintenance Planner Wizard in SQL Server Enterprise Manager to automate log shipping.
TaskTo create a standby server
- Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
- Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
- In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance container, and then expand the Databases container.
- Right-click Databases, point to All Tasks, and then click Attach Database.
The Attach Database – SelfPacedCPU dialog box appears.