How to set up and perform a log shipping role change (Transact-SQL)

How to set up and perform a log shipping role change (Transact-SQL)

Log shipping supports the changing of roles, which requires these basic steps:

1. Create a Data Transformation Services (DTS) package to copy the current primary server to the current secondary server.
   
   
2. Create a job to back up syslogins from the current primary server, copy the file to a directory on the current secondary server, and then execute the DTS package.
   
   
3. Perform the role change to set the current secondary server as the current primary server.

Before performing a log shipping role change, a maintenance plan for this log shipping pair must exist on the secondary server. A maintenance plan can be created using the Database Maintenance Plan Wizard, or by adding a server as a secondary server using the Add Secondary dialog box found in the user interface of the primary database maintenance plan.

To create a DTS package to copy the logins from the current primary server to the current secondary server

4. Create a DTS package on the current primary server using DTS Designer.

   The package should use the Transfer Logins Task, located in the list of tasks in the designer.

5. In the Transfer Logins dialog box on the Source tab, in the Source server list, enter the source server (the current primary server).
   
   
6. Click either Use Windows Authentication or Use SQL Server Authentication.
   
   
7. On the Destination tab, in the Destination server list, enter the destination server (the current secondary server).
   
   
8. Click either Use Windows Authentication or Use SQL Server Authentication.
   
   
9. On the Logins tab, click either All server logins detected at package runtime or Logins for selected databases.
   
   
10. Save the package.

To create a job to back up syslogins from the current primary server, copy the file to a directory on the current secondary server, and then execute the DTS package

1. Click New Job to open the New Job Properties dialog box on the General tab. On the current primary server, create a job owned by sa or a login with sysadmin rights to both servers.
   
   
2. On the Steps tab, click New to open the New Job Step dialog box, and then create the following job steps:
   • BCP Out

     In the Type list, select Operating System Command (CmdExec). In the Command text box, enter the command as follows:

     bcp master..syslogins out localpath\syslogins.dat /N /S current_primary_servername /U sa /P sa_password
     

     Click the Advanced tab, and then in the On success action list, select Go to the next step. In the On failure action list, select Quit the job reporting failure.

   • Copy File

     In the Type list, select Transact-SQL Script (T-SQL). In the Database list, specify master. In the Command text box, enter the command as follows:

     EXEC xp_cmdshell 'copy localpath\syslogins.dat destination_share'
     

     Click the Advanced tab, and then in the On success action list, select Go to the next step. In the On failure action list, select Quit the job reporting failure.

   • Transfer Logins

     In the Type list, select Operating System Command (CmdExec). In the Command text box, enter the command as follows:

     DTSRun /Scurrent_primary_server /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
     

3. In the New Job Properties dialog box, click the Schedules tab, and then create a job schedule that runs either one time or on a recurrent basis.

It is recommended that the job run as close to the time of role change as possible so that the job obtains the most current login information from the primary server.

To perform the role change to make the current secondary server the current primary server

You must be a SQL Server administrator to perform a server role change.

1. Run sp_change_primary_role on the instance of SQL Server marked as the current primary server. The example shows how to make the primary database stop being the primary database. current_primary_dbname is the name of the current primary database.

   EXEC sp_change_primary_role
       @db_name = 'current_primary_dbname',
       @backup_log = 1,
       @terminate = 0,
       @final_state = 2,
       @access_level = 1
   GO
   

2. Run sp_change_secondary_role on the instance of SQL Server marked as the current secondary server. The example shows how to make the secondary database the primary database. current_secondary_dbname is the name of the current secondary database.

   EXEC sp_change_secondary_role
       @db_name = 'current_secondary_dbname',
       @do_load = 1,
       @force_load = 1,
       @final_state = 1,
       @access_level = 1,
       @terminate = 1,
       @stopat = NULL
   GO
   

3. Run sp_change_monitor_role on the instance of SQL Server marked as the monitor. The example shows how to change the monitor to reflect the new primary database. new_source_directory is the path to the location where the primary server dumps the transaction logs.

   EXEC sp_change_monitor_role
       @primary_server = 'current_primary_server_name',
       @secondary_server = 'current_secondary_server_name',
       @database = 'current_secondary_dbname',
       @new_source = 'new_source_directory'
   GO
   

4. Run sp_resolve_logins on the instance of SQL Server now marked as the primary server (the former secondary server). You must run the stored procedure from the target database.

   The example shows how to resolve the logins on the new primary server against the logins from the former primary server. destination_path is the destination share specified in the Copy File job step. filename is the same as specified in the BCP Out job step. dbname is the name of the new primary database.

   EXEC sp_resolve_logins
       @dest_db = 'dbname', 
       @dest_path = 'destination_path', 
       @filename = 'filename'
   GO
   

The former secondary server is now the current primary server and is ready to assume the function of a primary server. The former primary is no longer part of a log shipping pair. You must add the former primary server as a secondary server to the new primary server to establish a log shipping pair between the two databases.

See Also

sp_change_monitor_role

sp_change_primary_role

sp_change_secondary_role

sp_resolve_logins