Log Shipping with SQL Server 2000

SQL Server 2000 Enterprise Edition fully supports log shipping setup and monitoring. The Enterprise Edition's Database Maintenance Plan Wizard includes a check box on the Transaction Log Backup dialog box that lets you specify shipping the log to another server. To use this option, you must first establish file shares on the source and destination servers for the transaction log folders. The wizard will then perform the full database backup and restore, create the transaction log backup job, and create the jobs on the destination server that copy and load the backups.

Related: Simple Log Shipping in SQL Server 2000 Standard Edition

As with the Microsoft BackOffice 4.5 Resource Kit (BORK) Log Shipping utility for SQL Server 7.0, these log-shipping jobs call the xp_sqlmaint extended stored procedure to perform their tasks. The wizard also creates alert jobs for the copy and load operations; these jobs call the same monitoring stored procedures as the BORK utility calls. However, the stored procedures and tables underlying SQL Server 2000's log-shipping functionality differ from—and have more consistent names than—the stored procedures and tables that provide the BORK functionality. SQL Server 2000 has seven tables to BORK's three and has four stored procedures for monitoring.

Overall, the SQL Server 2000 system is more polished than the BORK version. With SQL Server 2000, you can monitor log shipping through the Enterprise Manager instead of through the stored procedures. When you set up log shipping, the Database Maintenance Plan Wizard places a Log Shipping Monitor in the Management node of the server you specify. This monitor consists of a Tab dialog box that lets you observe log shipping's status and change settings for the source and destination servers. You can also view copy and load histories by right-clicking the Log Shipping Monitor in the details pane.

Like SQL Server 7.0, SQL Server 2000 doesn't offer automated support for failover or for changing the servers' roles so that log shipping goes the other direction. Instead, SQL Server 2000 supplies four stored procedures to assist in the failover process and relies on a new built-in Data Transformation Services (DTS) feature to transfer logins from the source to the destination server. You can use the stored procedures to resolve the logins on the former standby server, to change the servers' roles, and even to change which server the Log Shipping Monitor resides on.