Log Shipping in SQL Server 2000, Part 2

When your production database server goes down—as the result of planned maintenance or an unexpected event—you want to feel secure in the knowledge that the database is intact on a standby server. A well-designed log shipping operation, which ships a database's transaction logs from your primary server to a standby server, can give you this confidence. SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition support log shipping as a built-in Enterprise Manager utility. The Microsoft SQL Server 2000 Resource Kit comes with a set of unsupported stored procedures for log shipping in other SQL Server 2000 editions (see the sidebar "Simple Log Shipping in SQL Server 2000 Standard Edition," for details), and the Microsoft BackOffice Resource Kit (BORK) 4.5 provides an unsupported method of log shipping for SQL Server 7.0. In "Log Shipping in SQL Server 2000, Part 1," December 2001, I described how to set up, reconfigure, and monitor log shipping. In this article, let's look at how you can change the roles of the primary and secondary servers, how to fully reverse their roles, and where to place the monitor server for the most effective monitoring.

Changing Roles

Log shipping from a primary to a secondary server lets you use the secondary server in place of the primary server, if necessary. If a failure or a planned outage (e.g., for a hardware upgrade or a service pack installation) on the primary server requires you to take the production database out of service for an extended period of time, you can change the role of the secondary server database, bringing it into production as a substitute for the primary server. SQL Server 2000 Books Online (BOL) calls this operation a log shipping role change. During log shipping, you maintain the secondary server database in a nonrecovered state so that you can restore transaction logs from the primary server to the secondary server database. (As soon as you recover a database, you can no longer restore transaction logs to it.) In a role change, you recover the secondary server database and designate it as the new primary server database. You can also specify that the old primary server database will become the new secondary database. If the old primary database is undamaged, you can reestablish log shipping from the new primary server database to the old primary server, which becomes the new secondary server database. Let's call this type of switch a role reversal.

Although SQL Server 2000 provides an Enterprise Manager utility for setting up and monitoring log shipping, it provides only limited support for log shipping role changes, which you accomplish by manually applying system stored procedures from the msdb database. You can find complete directions for performing a role change in the SQL Server BOL article "How to Set Up and Perform a Log Shipping Role Change (Transact-SQL)." I've revised these directions into a set of six basic steps: transferring and exporting logins, demoting the primary server, promoting the secondary server, informing the monitor server of the role change, resolving the logins on the secondary server, and linking database access to permissions. Let's take a look at each step in detail.

Step 1: Transferring and exporting logins. First, BOL recommends building a SQL Server 2000 Data Transformation Services (DTS) package to transfer logins from the primary server to the secondary server and to resolve login SIDs across distinct servers. (The DTS Transfer Logins task, which you use to transfer the logins, is available only in SQL Server 2000 DTS.) You create and save the DTS package on the primary server, then set up the package execution by invoking dtsrun.exe through a SQL Server Agent job on the primary server. The package execution transfers the logins from one server to the other, but it doesn't resolve their login SIDs. (I describe resolving logins in a later step.) However, to be able to resolve login IDs later, you must first create a file containing an export of the primary server's syslogins table.

To export the logins to the secondary server, the BOL article recommends that you create a two-stage SQL Server Agent job: bcp out and copy. In the first step, you export the logins to a file by using bcp in native mode. In the second step, you copy the logins to a file on the secondary server that you can use later for resolving logins during the role change. At that point (Step 5), you use the sp_resolve_logins stored procedure to resolve login SIDs on the secondary server. After you create the job, you can run it at regular intervals (e.g., nightly) to keep an up-to-date exported file of logins on the secondary server in case you need to make a log shipping role change.

Step 2: Demoting the primary server. To take the primary server out of its role as the source of log shipping, you "demote" it to a lesser position. You can demote the primary server source database from a production server to a potential secondary server and remove it from log shipping by executing the sp_change_primary_role stored procedure on the primary server. Listing 1 shows the stored procedure that changes a log shipping database called Pubscopy from read/write mode to read-only standby mode, ready to receive transaction-log backups. The stored procedure removes the primary server database from the log shipping plan in several steps. The parameters tell the stored procedure to make one last transaction-log backup, terminate all users in the database, then put the database into a standby final state and into a multiuser access level. The stored procedure's return code states whether the BACKUP LOG statement succeeded.

Step 3: Promoting the secondary server. The next step is to promote the current secondary server database to a recovered state so that it can be used in place of the original production database and can become a possible primary log shipping database. On the secondary server, after you make sure no other users are accessing the database, you can execute the sp_change_secondary_role stored procedure, which Listing 2 shows.

The parameters cause the stored procedure to attempt to copy all remaining log files from the former primary server and load all the remaining copied transaction logs to the secondary server. Passing the @do_load = 1 parameter makes a last copy and load of all remaining transaction logs, and passing the @force_load = 1 parameter specifies the undocumented Forceload option on sqlmaint.exe. The @final_state = 1 parameter puts the new primary database in recovery mode, and the @access_level parameter sets the access back to multiuser. The @terminate = 1 parameter causes the stored procedure to terminate all users accessing the database by issuing the ALTER DATABASE command with the ROLLBACK IMMEDIATE option. However, if you keep your own Enterprise Manager connection to the database open while you execute the stored procedure, the ALTER DATABASE action will fail, so you might need to manually ensure that you've removed all your own connections to the database. Last, if the database is a publishing replication database, the @keep_replication = 0 parameter will maintain the server's replication settings.

The sp_change_secondary_role stored procedure requires exclusive use of the database and will fail if it doesn't have it. I've seen this procedure fail and report that the database is in use even when I knew that no users were accessing the database. Just rerunning the stored procedure solved the problem.

When the stored procedure finishes and brings the database online, it will send a message stating that the RESTORE DATABASE command succeeded. If you opted to make the secondary server a potential future primary server, the database maintenance plan will create a transaction-log backup job on the secondary server. After this job starts, transaction-log backup files will start appearing on the new primary server. You might need these files to establish log shipping back to the new secondary server.

Step 4: Informing the monitor server of the role change. SQL Server 2000 log shipping installs a monitoring utility on a monitor server, preferably a third server. To notify the monitor server of the role change, you now need to execute on the monitor server the sp_change_monitor_role stored procedure, which Listing 3 shows. Despite its name, this stored procedure doesn't change the monitor's role. Instead, the stored procedure changes the references to secondary and primary server file shares. That is, it deletes rows in the monitor server's log_shipping_secondaries table that referenced the old secondary server, then replaces the old primary server name with the new primary server name in the log_shipping_primaries table. The stored procedure doesn't insert rows into the log_shipping_secondaries table because a log shipping pair doesn't yet exist.

Step 5: Resolving the logins on the secondary server. Resolving the old primary server logins on the new primary server lets users access the new primary server. You can resolve the logins on the new production server by using the logins file that you exported in Step 1. The sp_resolve_logins stored procedure reads the exported logins file, then resolves the differing SIDs between the servers. For example, Listing 4 shows how you can execute the sp_resolve_logins stored procedure to resolve the logins on the newly recovered Pubscopy database. The BOL article says you must run this stored procedure in the target database. But in fact, sp_resolve_logins makes an unqualified reference to the syslogins view, so you must run the stored procedure from the master database.

Step 6: Linking database access to permissions. The BOL article ends its discussion of role changes at Step 5, but it omits an important step: coordinating database access with permissions for the transferred logins. To link the transferred and resolved logins with their corresponding database users and permissions, in the new primary server's production database, you need to execute the sp_change_users_login stored procedure once for each login:

USE pubscopyGOEXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName'

Executing this stored procedure ensures that the SQL Server logins link correctly to their corresponding usernames in the database.

At this point, you've successfully promoted the secondary server to its new role, and the old primary server is ready to become a secondary server. However, you still haven't established a log shipping relationship. You've made a role change but not a role reversal.

Reversing Roles

To accomplish a full log shipping role reversal, you only need to set up log shipping from the new primary server to the new secondary server. Because the new primary server contains a new database maintenance plan, your natural inclination is to add the new secondary server as a destination server in that plan. However, after repeatedly trying to add the new secondary server as a destination server, I found that the transaction-log backup job on the new primary server fails, and log shipping won't start from the new primary server to the new secondary server.

You need an alternative strategy. After you apply the log shipping role-change stored procedures and tasks that I detailed earlier, you can complete a full role reversal by setting up a new log shipping plan from the new primary server to the old secondary server. To set up the plan, you need to take the following steps:

After you set up the role reversal and the new log shipping pair, Enterprise Manager's Log Shipping Monitor might report that the new secondary server database is out of sync with the new primary server database. You'll receive this report if the time difference between the last transaction log loaded and the transaction-log backup from the new primary server exceeds the out-of-sync threshold. If you wait until the load job loads the latest backup file, the Log Shipping Monitor status will return to its usual error-free state.

Where to Place the Log Shipping Monitor

As I explained in Part 1, Microsoft strongly recommends that you put the log shipping monitor on its own server. On an independent server, the monitor can raise alerts if either the primary server or secondary server fails to do its job. If the monitor were on either the primary or secondary server, its reporting would depend on that server. And if the server that the monitor is running on failed, the monitor couldn't report an error. So, to let the monitor report errors from either the primary or secondary log shipping server, giving the monitor its own server is the better course of action. In addition, you can use the independent monitor server to monitor other log shipping pairs.

If you don't have a separate server for the monitor utility and need to put it on either the primary server or the secondary server, which server would best house the Log Shipping Monitor? Because your main concern will probably be detecting a log shipping failure caused by a failure of the primary server, the best location is the secondary server. If you put the monitor on the primary server and the primary server goes down, you won't be able to access the monitor, and the monitor can't alert you that log shipping has failed. So, if you must work with only two servers, the secondary server is a better location for the Log Shipping Monitor.

Sometimes you must ship transaction logs from one physical location to another, over some distance, to prevent disasters from affecting the secondary server. In that case, the best location for the Log Shipping Monitor is on its own server in a separate location, protected from disasters that might occur to either the primary or secondary server.

A Significant Improvement

Setting up and monitoring log shipping in SQL Server 2000 is supported by Enterprise Manager's built-in utilities. However, making role changes and role reversals requires extra work. Because Enterprise Manager's log shipping utility can't make a log shipping role change, you have to manually apply stored procedures. Furthermore, making a full role reversal requires workaround steps. And Enterprise Manager's log shipping utility doesn't provide a method for scripting the log shipping setup or removal, as it does for replication. Nevertheless, the SQL Server 2000 log shipping utility represents a significant improvement over earlier utilities by making setup and monitoring easy and intuitive.