Failover in SQL 2005 Log Shipping

The most important aspect in Log Shipping is Failover. Lets discuss it detail ! ! ! If the primary server in Log Shipping becomes unavailable or if it is going to be down due to some manual intervention, DBA should immediately perform the following steps for failover.

Step 1: Try to backup the Tail end of the transaction log in primary server with NORECOVERY option i.e perform a tran log backup if the primary is still accessible.

Backup log DBName to disk = "Local path or Network path" with NORECOVERY ---> A

Else execute the below T-SQL in secondary server to bring the secondary online,

Restore database DBName with Recovery ---> C

Step 2: If you were able to perform —> A in step 1 then proceed with —> B in step 2 to bring the secondary db online from read-only state.If you were able to perform only —> C in step1 then go to step 3

Restore log DBName from disk = "Local path or Network path" with RECOVERY ---> B

Step 3: The syslogins and sysusers table in primary and secondary server should be in sync otherwise the DB users and other application users from primary will not be able to login into SQL server or into the DB in secondary server after failover occurs.

Two ways are there to accomplish the above task namely

* Create the highly critical appplication users logins in the secondary server similar to primary just before configuring log shipping.Use the below sps to resolve orphaned users

USE
GO
sp_change_users_login @Action='update_one', @UserNamePattern='', @LoginName=''
GO

* Backup the master.. syslogins table to a text file.This file will be used to synchronize syslogins to sysusers in secondary when failover occurs.This can be done as following in the command prompt,

C:any path > bcp master . . syslogins out C:any pathsyslogins.dat -N -S servername -T

You can schedule it as a SQL Job to run @specific schedule preferable every 1 hr.You can make use of the sp_resolve_logins to resolve the old primary server logins on the new primary server.

sp_resolve_logins [ @dest_db = ] 'dest_db'
, [ @dest_path = ] 'dest_path'
, [ @filename = ] 'filename'

refer BOL for each parameter.

Step 4: Also Disable the log shipping jobs in the primary and secondary servers,once failover occurs.

Step 5: Once the failover occurs the original secondary server is configured as primary and log shipping is again newly configured from this new primary server(original secondary) to original primary(now secondary).

Step 6: When you once again want to revert to the original state of log shipping i.e original primary was primary and original secondary was secondary, you need take a full backup in new primary server(original secondary) and restore it in original primary and reconfigure the log shipping from the original primary to original secondary.