Role changes, role reversals, and positioning the monitor server
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," page 36, 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, InstantDoc ID 23056, 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.
Prev. page
[1]
2
next page 
