Log Shipping in SQL Server 2000, Part 1

Log shipping increases a SQL Server database's availability by automatically copying and restoring the database's transaction logs to another database on a standby server. Because the standby database receives all changes to the original database, it's an exact duplicate of the original database—out of date only by the delay in the copy-and-load process. You then have the ability to make the standby server a new primary server if the original primary server becomes unavailable. When the original primary server becomes available again, you can make it a new standby server—effectively reversing the servers' roles.

Related: Log Shipping with SQL Server 2000

In SQL Server 2000 Enterprise and Developer editions, Microsoft provides a log shipping utility in Enterprise Manager—as part of the Database Maintenance Plan Wizard. Previously, you needed to build your own log shipping system or—in the case of SQL Server 7.0—use the unsupported log shipping tools available in the Microsoft BackOffice 4.5 Resource Kit. (For information about those tools, see "Log Shipping with SQL Server 7.0," December 2000.) The new wizard eases the process of setting up, configuring, and monitoring SQL Server 2000 log shipping. (To verify the technical details in this article, I used SQL Server 2000 Enterprise Edition with Service Pack 1—SP1. However, according to the fix list, SP1 doesn't fix any bugs related to log shipping.)

Setting Up Log Shipping

The primary server is the production server on which the real work takes place; this server holds the source database. The secondary server holds the destination database to which you copy and restore the source database's transaction logs. A monitor server monitors both the primary and secondary servers. Unlike SQL Server 7.0's method of monitoring log shipping from the secondary server, SQL Server 2000 log shipping uses Enterprise Manager's Log Shipping Monitor utility to monitor each log shipping pair. Microsoft recommends that you install this utility on a separate monitor server.

You use Enterprise Manager's Database Maintenance Plan Wizard to set up SQL Server 2000 log shipping. But before you can use the wizard, you must make some initial preparations. To begin, carry out the following steps:

After you complete these preliminary preparations, you're ready to use the Database Maintenance Plan Wizard to set up log shipping. You can view the process as a series of five steps, which Figure 1 shows.

The first two steps are optional. If you haven't yet synchronized the source and destination databases, Step 1 carries out that task by making a one-time backup of the source database. In Step 2, the wizard copies that backup to the secondary server and restores it to the destination database.

The wizard always performs the remaining three steps. In Step 3, the wizard creates a SQL Agent job on the primary server. This job periodically backs up the transaction logs to disk files. The wizard also creates a log shipping database maintenance plan on the secondary server. This plan consists of two SQL Agent jobs: one to copy the transaction-log files to the secondary server (Step 4) and another to restore the transaction logs to the destination database (Step 5).

These steps create a log shipping pair (i.e., two databases in a log shipping relationship). To provide further redundancy or to set up a reporting server, you can form other log shipping pairs by setting up log shipping from the primary server to additional secondary servers.

Step by Step

To see how the wizard works, let's step through it screen by screen. Open Enterprise Manager, expand the primary server node, drill down to the Management node, and launch the Database Maintenance Plan Wizard.

In the wizard's first screen, which Figure 2 shows, select the source database, then select the Ship the transaction logs to other SQL Servers (log shipping) check box at the bottom of the screen. You can build a database maintenance plan for only one source database at a time. The check box is available only when you set the selected source database to the full or bulk_logged recovery model because only those models permit transaction-log backups.

The next several screens present options to associate full database backups and maintenance activity with the database maintenance plan. However, a plan with more than one function can be confusing, so I suggest you keep your log shipping plans separate from other plans.

In the Specify Transaction Log Backup Disk Directory screen, select the Use this directory check box and navigate to the primary server's transaction-log folder. Make sure the specified Remove files older than duration is sufficient to keep the log files on the primary server until you can back them up as part of your regular backup strategy. If you specify too short a duration and if the copy job on the secondary server fails, the primary server's SQL Agent job will delete transaction-log files before SQL Server can copy them to the secondary server, and log shipping will fail. Specify the default .trn extension for the transaction-log files, which the plan will name according to the format dbname_tlog_yyyymmddhhmm.trn.

The next screen, Specify the Transaction Log Share, appears only when you've specified that the plan will install log shipping. On this screen, you must identify the file share on the primary server. You can use the ellipsis (...) button to navigate to the file share.

The Specify the Log Shipping Destinations screen lets you add your secondary server or servers, one at a time. Click Add to open the Add Destination Database dialog box, which Figure 3 shows and in which you enter all the secondary-server information. The Server Name text box shows the secondary-server name you registered in Enterprise Manager as part of your preparation steps. In the Directory text box, enter the name of the secondary-server directory that will receive copies of the source database's transaction logs. This name is a local pathname, not a file share. You can choose the option to Create and initialize new database on the secondary server, in which case SQL Server will copy and restore the initial database backup. Or you can choose Use existing database (No initialization) if you've already performed the database restore. If your database is large, you might want to make your own backup and restore it during off-hours.

The destination database must be in a nonrecovered state for SQL Server to restore transaction logs to it. You have two options regarding the database load state: No recovery mode or Standby mode. No recovery mode means that users can't query the database; transaction-log restores will be the only activity that occurs on it. Standby mode leaves the database in a read-only state so that you can query it when you aren't restoring transaction logs. The Add Destination Database screen also presents an option to Terminate users in database (Recommended) during the database restore (if you're using an existing database) or during transaction-log restores. During a database or transaction-log restore, the restore process is the only "user" permitted in the database. Therefore, Microsoft recommends that you use this option because the presence of other users in the database will delay the restore.

You might also want to select the Allow database to assume primary role option, which lets the destination database become a new log shipping source database and thus permits a possible future role reversal between the primary and secondary servers. When you select this option, specify the secondary server's transaction-log file share as the location for transaction-log backups from the new source database.

On the next screen, Initialize the Destination Databases, you can choose to use a recent backup or to make a new one. For large databases, using an existing backup might be more convenient. However, all the transaction logs that occurred since that backup must reside in the primary server's transaction-log file share, in the correct naming style, so that the wizard can copy and restore them to the secondary server. For smaller databases, letting the wizard generate the backup is much easier.

On the Log Shipping Schedules screen, you set the frequency of transaction-log backups for the source database and of the SQL Agent copy and load jobs that the wizard creates on the secondary server. Log shipping frequencies can be as granular as once per minute, but once every 5 minutes is a more common choice for larger databases.

The Log Shipping Thresholds screen presents options to set the acceptable latency times for transaction-log­backup and copy and restore jobs. When the threshold times are exceeded, the Log Shipping Monitor dialog box on the monitor server raises an alert.

Speaking of the monitor server, the next screen, Specify Log Shipping Monitor Server Information, lets you specify which server to use as your monitor server. Be careful: You might be tempted to simply accept the default, but that default is often the primary server. Typically, you don't want to use the primary or secondary server as a monitor server because you won't be able to determine the current state of log shipping if either of those servers becomes unavailable.

After a few reporting screens, the wizard's final screen lets you choose a plan name. I suggest that for clarity, you put the phrase log shipping somewhere in this name. You might not want to include the server names in the plan name if you want to perform a future server-role change. Click Finish. Now, the wizard automatically sets up log shipping from your primary to your secondary server and also sets up the Log Shipping Monitor on the monitor server.

Changing a Log Shipping Configuration

You can use your database maintenance plan's Properties dialog box to change your log shipping configuration. The Transaction Log tab offers options for changing the configuration of the log shipping transaction-log backups. The Log Shipping tab shows you the log shipping pair you originally created for the plan, as well as any other pairs you might have added to the plan. This tab also contains options to add a new destination database (thereby creating a new log shipping pair), to delete a selected log shipping pair, to edit current log shipping pair properties, and to remove log shipping entirely.

When you choose Edit from the Log Shipping tab, you open the Edit Destination Database dialog box. On the dialog box's General tab, you can view and change the secondary-server transaction-log directory and future primary-server file share locations that you chose during setup. On the Initialize tab, you can change the recovery mode and the secondary server's copy and restore frequencies. On the Thresholds tab, which Figure 4 shows, you can set threshold durations. The Out of Sync Threshold sets the maximum permitted duration (between the latest source database transaction-log backup and the most recent transaction-log restore) before the Log Shipping Monitor will generate an alert. (You can also set this parameter from the Log Shipping Monitor.) The Load Time Delay, File Retention Period, and History Retention Period values all relate to the secondary server.

The monitor server plays an important role in these configuration options. The Log Shipping tab depends on the monitor server for much of its information, so you can't change log shipping configuration values when the monitor server is unavailable. When I ran SQL Server 2000 Profiler on the monitor server, I found that the primary server connects to the monitor server to retrieve existing log shipping plan information from the log shipping tables on the monitor server. Therefore, to change a log shipping plan's settings, you must make sure that the monitor server is available to Enterprise Manager.

Inspecting and Monitoring Log Shipping

If you're accustomed to SQL Server 7.0 log shipping or you've created your own log shipping system, you probably monitor log shipping activity from the secondary server. However, SQL Server 2000 log shipping also provides a special Log Shipping Monitor utility that you typically place on a separate monitor server.

Where does SQL Server 2000 store log shipping information? A total of seven log shipping tables reside in the msdb database in SQL Server 2000 Enterprise and Developer editions:

Each of these tables exists on the primary, secondary, and monitor servers. Each server uses only some of the tables to store data, depending on the role the server plays in log shipping.

Inspecting log shipping on the primary server. From Enterprise Manager, you can connect to the primary server and inspect and monitor log shipping. If a database is involved in log shipping, the General tab of that database's Properties dialog box tells you the database's role (i.e., source or destination) as well as which server houses the Log Shipping Monitor. You can view the state and history of the log shipping transaction-log backup job in Enterprise Manager's SQL Server Agent, Jobs node.

The primary server populates only two of the msdb log shipping tables. In log_shipping_databases, SQL Server inserts a row linking the database maintenance plan ID to the source log shipping database. In the log_shipping_monitor table, SQL Server inserts a row containing the monitor server name and type of login.

Inspecting log shipping on the secondary server. The log shipping plan resides on the secondary server. From that server, you can monitor the SQL Agent jobs that copy transaction-log files to the server and restore those logs to the destination database. You can also inspect the destination database's Properties dialog box to determine the database's role in the log shipping process.

On the secondary server, SQL Server uses four of the msdb log shipping tables. After SQL Server creates the log shipping plan, it inserts a row into the log_shipping_plans table, specifying the primary and secondary server names, file locations, and the copy and restore job IDs (which it gets from the sysjobs system table on the secondary server). In the log_shipping_plan_databases table, SQL Server links the plan to the source and destination database names and stores information about the last files copied and loaded. The log_shipping_plan_history table records each log shipping plan's copy and restore event, along with information such as whether the job succeeded. In the log_shipping_monitor table, SQL Server also inserts a row that references the monitor server.

If you chose (when you set up log shipping in the Database Maintenance Plan Wizard) to let the destination database assume a primary role, you'll see one important additional item on the secondary server: another database maintenance plan with the same name as the plan you created but without log shipping enabled. You'll also see a disabled SQL Agent job for making transaction-log backups of that database. You might find these additional items confusing. Despite having the same name, this additional plan isn't the same maintenance plan as the one you created. SQL Server simply holds this second plan in reserve in case you make a primary- and secondary-server role change.

Monitoring log shipping on the monitor server. After log shipping is in place, SQL Server enables Enterprise Manager's Log Shipping Monitor utility on the monitor server. In addition, SQL Server creates two SQL Agent alert jobs: one for backup and the other for out-of-sync conditions.

To use the utility, open Enterprise Manager and connect to the monitor server, drill down to the Management node, and select the Log Shipping Monitor. When you select the utility, it presents a list of log shipping pairs. Right-click a log shipping pair to view its backup, copy, and restore histories. The copy and restore histories are especially useful because they give you quick access to more detailed error information than you can find in the histories of the secondary server's SQL Agent copy and restore jobs.

When you open a pair's Properties dialog box and go the Status tab, which Figure 5 shows, you can view the status of the pair's backup and copy and restore processes. The status can be Normal or Out-of-Sync. If the SQL Server Agent hasn't yet copied or restored a transaction log, the dialog box will display the log-file name as first_file_000000000000.trn. This isn't the name of a real file but simply a way for the utility to indicate that the SQL Server Agent hasn't processed any files yet. The Status tab also shows the Backup, Copy, and Load (i.e., restore) deltas in minutes at the current time. The information on this tab doesn't refresh automatically, so you must close and reopen the dialog box to refresh the data.

SQL Server uses only two of the msdb log shipping tables to store log shipping pair information. In each table, SQL Server assigns an ID to link entries, and a foreign key constraint in log_shipping_secondaries references the log_shipping_primaries primary_id. (These two tables are the only log shipping tables that contain a foreign key relationship.) The log_shipping_primaries table contains a row for each log shipping source database, its transaction-log backup job status, and planned outage information (which you can use to avoid unnecessary alerts). The log_shipping_secondaries table contains a row for each destination database that belongs to a particular log shipping source database. The linked rows form the log shipping pairs that appear in the Log Shipping Monitor.

Each log shipping pair's Log Shipping Pair Properties dialog box also contains a Source tab and a Destination tab, which let you adjust information concerning thresholds for backup failure alerts and out-of-sync alerts, respectively. These alerts refer to two SQL Agent jobs running on the monitor server: Log Shipping Alert Job - Backup and Log Shipping Alert Job - Restore. By default, each of the jobs executes once per minute. These jobs generate an error in the Windows 2000 or Windows NT Application log when any log shipping pair's backup delay exceeds the backup-alert threshold or when the pair's copy and restore process is delayed beyond the out-of-sync threshold.

Removing and Reinstalling Log Shipping

To remove log shipping from a database maintenance plan, open the plan's Properties dialog box, go to the Log Shipping tab, then click Remove Log Shipping. This action removes the SQL Agent copy and restore jobs from the secondary server, clears out data from the log shipping tables on that server, and removes all related information from the Log Shipping Monitor. However, the action leaves the primary server's SQL Server Agent transaction-log backup plan in place. Deleting the database maintenance plan removes that job. To remove the Log Shipping Monitor from the monitor server, manually delete the applicable rows from the log_shipping_primaries and log_shipping_secondaries tables in the monitor server's msdb database.

If you enabled a destination database to assume the role of a source database when you set up log shipping in the Database Maintenance Plan Wizard, the secondary server's database maintenance plan and its transaction-log backup job remain on the secondary server after you delete the plan on the primary server. To remove these items, delete the secondary server's log shipping database maintenance plan.

When you experiment with SQL Server 2000 log shipping, chances are you'll occasionally abort the installation process. When you do, some data might remain in each server's log shipping tables and can interfere with subsequent attempts to install log shipping. (For details, see the Microsoft article "BUG: All Changes May Not Be Rolled Back When Log Shipping Maintenance Wizard Fails" at http://support.microsoft.com/support/kb/articles/q298/7/43.asp.) To guarantee that these remnants are removed fully, delete all related data from the msdb log shipping tables on each server.

Coming Up: Role Reversals

Now that you've set up and configured log shipping, you need to know how to reverse your primary and secondary servers' roles if necessary. In Part 2 of this series, I'll explain how to perform a log shipping role change, how to perform a log shipping role reversal, and how to use log shipping in SQL Server 2000 Standard Edition.