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.

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:

1. Identify the log shipping pairs (i.e., the primary server­secondary server combinations that you want to participate in log shipping). Although you can set up log shipping between two databases on one server, the information in this article assumes that you intend to set up log shipping from one server to another.
2. Identify a monitor server, which should be separate from both the primary and secondary servers.
3. Establish security to all the servers. The Windows account you use to set up log shipping must have SQL Server systems administrator (sa) privileges on all the servers.
4. Create primary and secondary file shares. First, create a share for the folder in which the source database's transaction logs will reside. Second, create a share for the secondary-server folder to which you plan to copy and restore the transaction-log files. To clarify the file share's purpose, specify the server and database names in the file share. If file shares already exist, you might want to delete or move other files, especially old transaction-log backup files, from the shares. Grant permissions on these file shares to the Windows account that each server's SQL Agent is using.
5. Decide how to create and initially synchronize the destination database. You can let the log shipping setup process create and initially synchronize the destination database, or you can restore the initial full database backup manually.
6. Register all three servers (i.e., primary, secondary, and monitor) in Enterprise Manager.

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.