The stored procedure requires either the plan ID or the plan name. I agree with the BORK documentation, which recommends specifying the plan name instead of the GUID for clarity. The source and destination database names are required, although the stored procedure doesn't explicitly test for them. The optional load delay defaults to 0 minutes, but you can force a longer delay before restoring the transaction log backups after a copy.
The optional load_all flag defaults to 1, meaning that each time the job runs, it will try to load all the transaction logs available. This option can help the system catch up with transaction log restores if it falls behind because, for example, you turned off the copy and load jobs for a while or the jobs took longer than expected. The source server name provides information for the backup_movement_plan_history table, and the retention period specifies how long to keep restored transaction logs. Fortunately, the log-shipping process doesn't delete any transaction logs that it hasn't restored, no matter how old the logs are.
Troubleshooting
After you've executed the sp_add_db_to_backup_movement_plan stored procedure, log shipping will start—unless you've made a mistake in the installation process. In my experience, the most common mistake is sending inaccurate parameter names, such as directories or maintenance plan names, to the stored procedures. But troubleshooting log shipping can be challenging because xp_sqlmaint's error messages aren't always clear. Here's a sample cryptic error message from the transaction log restore (i.e., load) job history:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Luckily, as BORK's Log Shipping documentation says, only a few things can go wrong with log shipping: the transaction log backup plan can fail, the copy job can fail, or the load (restore) job can fail.
The BORK documentation contains a helpful section about troubleshooting log shipping, and the BORK utility provides stored procedures that help you monitor and troubleshoot log shipping. After you apply the BORK instlog.sql script, you need to apply the log_ship_sprocs.sql script, which creates four stored procedures: log_ship_status, log_ship_entity_log, log_ship_history_purge, and log_ship_alert. Although the scripts don't require you to, creating and running these stored procedures in msdb is safest because you don't risk accidentally creating the procedures in another database.
Log_ship_status accepts optional primary server and primary database parameters and returns a set of useful status values about your log-shipping plans. Returned status values include a column called delta, which shows the delay, in minutes, between the current time and the last transaction log restore for a given plan. A large delta can indicate problems, such as a breakdown in the copy or load process.
Log_ship_entity_log returns a snapshot of the backup_movement_plan_history table, filtered by parameters that distinguish the source server and database and the destination server and database. Log_ship_history_purge removes history rows older than the number of days you specify in a parameter; the parameter value must be a number greater than 1. Finally, log_ship_alert generates a SQL Server error log and a Windows NT Application Event Log message when the log-shipping delta (less the load delay) exceeds an alert length that you specify.
These stored procedures can help you track down many log-shipping problems. For example, after log shipping is operational, you might have activity on the production server that makes your transaction logs so large that they don't restore quickly on the standby server. This problem might show up as large delta values from the log_ship_status stored procedure or as a large-delta alert from the log_ship_alert stored procedure.
When you first start using log shipping, you'll probably have to experiment before you fully understand the parameters involved and arrive at the best settings for your environment. And you'll probably have to delete your flawed log-shipping plans and start over. Unfortunately, the BORK utility doesn't provide a delete stored procedure. You can manually delete a log-shipping plan by truncating the tables (truncate backup_movement_plans last because it's part of a foreign key contraint), then manually deleting the jobs, but that method can become tedious. Listing 1 shows a simple stored procedure to delete backup plans and their jobs. To make the listing brief, I left out error handling after the delete statements; you'll want to add your own error-handling routines to the procedure.
Failing Over
Sooner or later, you'll need to use log shipping to fail over. Server failures and server hardware upgrades force a failover. Even service pack installations and other diagnostic activity against a production server can cause you to rely on your standby server. Testing your failover process and documenting the steps so that you and others can perform the process under stressful conditions is essential. Here are the three basic steps for failing over to a warm standby.
Capture the last good transaction log backup from the production server, then apply the backup to the standby server with recovery. If you can get one last transaction log backup from the production server before it goes down, you might be able to fail over to the standby server with almost no data loss. However, if the SQL Server is suddenly unavailable, you'll lose all transactions that weren't finished before the last good transaction log backup.
Apply server logins and database permissions on the standby server to match those of the production server. First, you must add the logins to SQL Server on the standby system or make sure that the logins are already there. Depending on your environment, you can try using the sp_change_users_login stored procedure with the AutoFix option to link the database users in the standby server to the logins. However, Microsoft doesn't recommend this stored procedure in "security-sensitive" situations. (For more information about potential problems, see the Microsoft article "FIX: Sp_Change_Users_Login with Auto_Fix Fails When Run with Local Cursor Database Option Set" at http://support.microsoft.com/support/kb/articles/Q247/2/35.asp.)
A surefire way to transfer logins and permissions is to apply a script just for that purpose. The sp_revokedbaccess stored procedure, which SQL Server 7. 0 Service Pack 2 (SP2) fixed, removes orphaned users from a database. You can then write a script that uses the sp_grantdbaccess stored procedure along with the GRANT command to assign permissions. Don't forget to test the logins to make sure they can connect to and reach the data on the standby server.
Connect to the standby server. Unlike a server in a cluster, the standby server will have a different name and different IP address from the failed production server, so you'll have to change the client applications' data sources. To make this change easier, you can use a connection broker, which is a table or a file that tells the clients which SQL Server system they should connect to.
Speaking from Experience
I've been working with a log-shipping installation in production for more than 9 months on a set of databases holding more than 300GB of data. Some of the databases approach 35GB in size. Large databases present the greatest challenges when you're performing management tasks, including log shipping. Here are some of the things we've learned through experience.
Size matters. Databases larger than, say, 20GB can take a long time to back up, copy, and restore. A 30GB database can take more than an hour to back up and can produce a backup file nearly as large as the database itself. Copying a 30GB file to the standby server can take hours unless you have a fast connection between the servers. Restoring the database across the same link can take much longer than backing up the database, and the copy or restore process might generate extra workload for the production server's I/O subsystem. We found that, over time, setting up log shipping for large databases one by one was easier than trying to set them up all at once. With smaller databases (a few gigabytes or less), setting up multiple log-shipping processes goes quickly and isn't a problem.
Avoid interference. Long-running database backups block scheduled transaction log backups, producing error messages in the SQL Agent jobs. We decided to schedule transaction log backups only outside the database backup window.
The database as a stage. Loading large amounts of data into the production server's databases and performing massive updates on the loaded data often dramatically increased the size of the transaction log backup files. As a result, when we performed log backups during the data-loading and massaging process, the standby server restore jobs spent a lot of time in a rollback state, handling the massive inserts and updates. This situation caused an accumulation of backup jobs on the standby server and often delayed log shipping by up to 24 hours. Our solutions were to load data that needed massaging into a staging database that doesn't need log backups and to shut down the transaction log backup jobs on any other log-shipping databases during large data loads.
No reporting, please! You can manually restore the database and transaction logs to the standby server with STANDBY, which puts the database into read-only mode, or with NORECOVERY, which leaves the database in a non-operating mode. However, in STANDBY mode, if any user is querying the database or maintaining any kind of connection to it, the log-shipping load job can't restore any logs because restoring a transaction log requires exclusive use of the database. So, you might be tempted to restore the standby database with NORECOVERY to prevent users from reading the data. Unfortunately, when xp_sqlmaint restores the transaction logs, it restores them with STANDBY, making the databases read-only. Because our transaction log backup frequency is 5 minutes, we had to implement a policy that users (and developers) can't access the standby server databases.
Multiple databases with STANDBY. When you specify a database restore with STANDBY, you must also specify an undo file name. We found that if we tried to restore more than one database at a time, the databases couldn't use the same undo filename. If they did, the restores failed. We perform our full database restores with NORECOVERY instead, even though the standby databases ultimately end up in read-only mode when xp_sqlmaint restores the transaction logs.
Yet another service pack. When you install a SQL Server 7.0 service pack, all databases must be in a recovered mode. Consequently, to apply a service pack, you must recover the databases, apply the service pack, reinitialize the standby server's databases from a full database backup, then restart log shipping.
Don't detach. Using sp_detach_db on a standby database that is in NORECOVERY or STANDBY mode produces the error message
Server: Msg 947, Level 16, State 1, Line 0
Error while closing database 'pubscopy' cleanly.
Successfully detached database 'Pubscopy'.
When you then reattach the database, it will be in a recovery mode. Subsequent attempts by the load job to apply transaction log backups will fail. Essentially, using sp_detach_db on a standby database that is in NORECOVERY or STANDBY mode breaks log shipping, and I haven't seen an explanation from Microsoft about the error message. Watch out, because the login that attached the database will also own the attached database.
With a little setup work, you can implement log shipping as an inexpensive and reliable high-availability solution for SQL Server 7.0 that provides nearly realtime disaster recovery. And log shipping is easier to set up and manage in SQL Server 2000. Watch SQL Server Magazine for an upcoming article about log-shipping enhancements in SQL Server 2000.