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.