SQL Server Magazine

Log shipping is a robust yet low-tech, low-cost way to achieve high availability and disaster protection in your SQL Server environment. Log shipping automatically ships—copies and restores—a production server's transaction logs to a standby server, which stands ready to take the place of a failed production server. If you keep your standby server's copy of production data current (within minutes of the production data), you have a warm standby.

For years, SQL Server developers and DBAs have had to create customized scripts to implement log shipping. But with SQL Server 7.0, Microsoft provided undocumented hooks into the extended stored procedure xp_sqlmaint to help automate log shipping. Xp_sqlmaint calls the Sqlmaint.exe utility, as do jobs that the Database Maintenance Wizard creates. In fact, the Microsoft BackOffice 4.5 Resource Kit (BORK) Log Shipping utility consists of scripts and instructions that use these hooks into xp_sqlmaint for creating a mostly automated log-shipping solution. This article covers log shipping with SQL Server 7.0, as BORK defines the process. You can get BORK from Microsoft Press for $250 retail (about $130 street price).

Microsoft developed the BORK Log Shipping utility, as it did all its resource kit utilities, for internal use at Microsoft; Microsoft Product Support doesn't support the utility. And you'll find the BORK Log Shipping utility rough around the edges. Although it automates much of the log-shipping process, the utility requires a fair amount of manual setup and administration. However, SQL Server 2000 Enterprise Edition fully supports log shipping and provides a more polished interface than the BORK utility. For a summary of SQL Server 2000's log-shipping functionality, see the sidebar "Log Shipping with SQL Server 2000," page 46. Watch for an upcoming article in SQL Server Magazine that covers SQL Server 2000 log shipping in detail.

How Log Shipping Works
In a log-shipping implementation, failing over to the standby server is a manual process. If you require automated failover, a better solution is Microsoft Cluster Services (MSCS), which automatically fails over one cluster node to another cluster node. However, the clustering solution costs more than log shipping, requires additional specialized hardware, and forces a closer coupling, or dependency, of primary and secondary servers. To set up log shipping, all you need is the BORK utility, enough disk space on your production and standby servers to store the database and transaction log backup files, and a network connection between the servers.

Before jumping into how you set up log shipping, let's quickly look at the five basic steps of the log-shipping process, which Figure 1, page 46, shows.

Perform a full database backup of the production database. To initialize log shipping, you must first perform a full database backup of the production database. The production database must have the Truncate log on checkpoint and Select into/bulk copy database options off so that SQL Server will log all data changes. Transaction log backups are impossible to perform when you have these database options on.

Restore the full backup to the standby server without recovery. You must restore the full database backup to the standby server without recovery—that is, with either the NORECOVERY or the STANDBY option. The NORECOVERY mode prevents users from reading data from the database during the restore. But the STANDBY mode lets users read the data already on the standby server. Using STANDBY mode when you restore the full database backup to the standby server is fine. However, any transaction log restore requires exclusive use of the database, so letting users query a standby database can delay the log-shipping transaction log restore.

Perform transaction log backups to files on the production server. When using the BORK Log Shipping utility, you must name the transaction log backup files according to the convention: dbname_tlog_yyyymmddhhmm.trn (the default for Database Maintenance Plans). The timestamp in the file's name lets the log-shipping process restore the transaction log files in sequence.

Copy the transaction log backup files to the standby server. A SQL Agent job running on the standby server uses xp_sqlmaint to copy the files to the standby server. (I tell you how to set up this job later.)

Restore the transaction log backup files to the standby server without recovery. Another SQL Agent job running on the standby server uses xp_sqlmaint to restore the transaction logs.

Log shipping is very reliable. After you have the previous steps in place—and as long as the transaction log backup chain remains unbroken—log shipping will run indefinitely. However, installing SQL Server service packs and recovering the standby database will interrupt the process and require you to reinitialize the standby server and restart log shipping.

For smaller databases, the reinitializing process goes quickly, but for larger databases, the process can be time-consuming. Still, after you have the log-shipping process running, you can continue to perform full and differential database backups of your production database, in accordance with your usual backup schedule, without interfering with log shipping. You can then archive these database backups without restoring them to the standby server.

Log shipping's only significant vulnerability lies in the transaction log chain. You must apply the transaction logs in sequence, and all restores must be successful, or the process will fail and you'll have to reinitialize the standby server. Ensuring that your backup and restore practices protect the continual log-shipping process is vital.

Installing Log Shipping
Now that you've seen an overview of how log shipping works, let's go back to the beginning. To install log shipping, complete the following six steps.

Perform a full database backup. As I noted earlier, you must make sure that the production server database has the database options Truncate log on checkpoint and Select into/bulk copy off. You then need to perform a full database backup, preferably to a disk file.

Apply the full database backup to the standby server. Restore the full backup to the standby server database without recovery. For example, if you're restoring a database called Pubscopy to a standby server, you can specify either Leave database nonoperational, but able to restore additional transaction logs or Leave database read-only and able to restore additional transaction logs in the Enterprise Manager Restore database dialog box, as Figure 2, page 47, shows. The Leave database nonoperational option is equivalent to the T-SQL command

RESTORE DATABASE Pubscopy
FROM 'd:\mssql7\backup\pubscopy.bak'
WITH NORECOVERY

and the Leave database read-only option is equivalent to the T-SQL command

RESTORE DATABASE Pubscopy
FROM 'd:\mssql7\backup\pubscopy.bak'
WITH STANDBY

Create a job that periodically backs up production database transaction logs. Create a job on the production server to regularly back up transaction logs to disk. The easiest way to do this (and the method the BORK Log Shipping utility supports) is to create a Database Maintenance Plan for backing up the production database's transaction logs at a desired interval. Remember to note which directory you save the backups to and whether you specified a directory for each database's transaction log backup file. If you have many databases involved in the log-shipping process, specifying a subdirectory for each database's files (as Figure 3 shows) can help you manage the many backup files the system will produce.

Make sure you give the Database Maintenance Plan a clear, descriptive name—something other than the default Database Maintenance Plan 1—such as Pubscopy log shipping. You can specify that the job run as often as once per minute, but that setting means 60 transaction log files per hour, per database. Although you might be tempted to run the backup job that frequently to keep your server as warm as possible, for large, active databases, a job frequency setting in the range of 5- to 15-minute intervals is more manageable.

Apply the BORK Log Shipping scripts on the standby server. Using Query Analyzer or OSQL, execute the BORK Log Shipping install scripts in the msdb database on the standby server. The first script, instlog.sql, creates three tables in msdb and also creates two stored procedures: one to create the backup (log-shipping) plan and another to add a particular database to the plan. The second script, log_ship_sprocs.sql, creates stored procedures for monitoring log shipping (I cover these stored procedures a little later).

The instlog.sql script creates three tables: backup_movement_ plans, backup_movement_plan_databases, and backup_movement_plan_history. Figure 4, page 48, shows the tables' columns and relationships. Backup_movement_plans contains a plan name, which should be the same as the name of the Database Maintenance Plan you created earlier. The table's primary key is an automatically assigned globally unique ID (GUID) called the plan ID. The table also contains the source and destination directory names for the transaction log files. Backup_movement_plan_databases contains the plan ID along with source and destination database names and status information about the last copy and last load. (Note that the backup_movement_plan_databases table doesn't have a primary key.) The third table, backup_movement_plan_history, contains a history of copy and load events for a given plan, including durations and error messages.

BORK also comes with 46 pages of log-shipping documentation, but many people find some of the information redundant, confusing, or—in the case of some raw output—plain unhelpful. The documentation contains an example; however, the example shows how to set up log shipping between different databases on the same server instead of the more common production scenario of having a production server that has a database named the same as a database on a standby server.

Create a backup movement plan on the standby server. You create a backup movement plan on the standby server by executing the sp_create_backup_movement_plan stored procedure in the msdb database. (BORK's instlog script also provides this stored procedure.) The stored procedure's purpose is to add the backup plan name to the backup_movement_plans table and to create the transaction log copy and load jobs in SQL Agent.

   Prev. page   [1] 2 3     next page