Steps to Failover and Failback a Log Shipping Database in SQL 2000

Recently for one of our customers we had to perform a DR drill activity. To give a background on the environment, they were using SQL 2000 Database as a backend of SAP Application and the SQL Database was log shipped to Remote DR server.

We had also customized the Log Shipping to add compression/decompression logic which is introduced in SQL 2008 inbuilt but which was not present in SQL 2000 such that after the T log backup job runs on the primary server, we compress the files in the same location with Winzip, the copy job copies the compressed file to the remote DR following which it unzips the copied file, the unzipped file is then restored by the Restore Job on the secondary server.

In DR drill activity we wanted to test and validate the data on the secondary database by failing over to secondary database where we perform some transaction on the secondary database and again failback to Primary server to again validate that reverse log shipping is working fine. So basically we wanted to perform a role reversal and test the setup.

However we had a challenge that database size was huge approx. 8TB in size wherein capturing a Full backup and restore operation takes around 8-10 hours of time so we couldn’t afford to have a downtime or time when primary database is exposed with secondary backup site.

We created following steps or Plan to ensure smooth failover and failback

1. Script out all the Logins from the Production database and create the Logins in the Secondary database.

If the Windows Logins are already created on the Secondary database, don’t recreate them but if SQL Logins are already created drop the logins and recreate them since the SIDs for SQL Logins has to be same in both the servers.

Use Method 1 in the following KB article http://support.microsoft.com/kb/246133

2. Stop all the activity of the Primary database but shutting down the SAP Application.

3. Confirm no activity on the Primary database by using the following command

Use master
Go
select * from sysprocesses where dbid = DB_ID('Northwind')

4. Disable all the Log Shipping Jobs viz Backup Job on the Primary Server, Copy and Restore Job on the Secondary Server

5. Take Full Database Backup on the Production server (Rollback Strategy)

6. Run the sp_change_primary_role on the Primary Database

USE master
GO

EXEC msdb.dbo.sp_change_primary_role
@db_name = 'Northwind',
@backup_log = 1,
@terminate = 1,
@final_state = 3,
@access_level = 1

Please Note: The above step is very Important and if we get any error in the above step don’t move ahead until the error is fixed.

The above step creates a Backup of the Log and that log backup could be found in the default Backup location which is C:Program FilesMicrsoft SQL ServerMSSQLBACKUP.

The last backup will not be created in the Log Shipping Backup share. If we miss the last t-log backup and open the secondary database there can be data loss and more importantly failback will not be possible. (In this case the only option to reconfigure Log Shipping)

7. Check from Enterprise Manager whether the Primary database has been converted to read-only mode.

8. Find all the backupset performed on the Primary database using the following query

select physical_device_name,* from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
order by backup_set_id desc

You might find the latest backup in the location c:Program FilesMicrosoft SQL ServerMSSQLBACKUP

9. Manually copy the TRN files from Primary Server which have not reached the Secondary server location. Especially the last backup file and copy it in the destination Tlog folder

10. Run the Restore Job manually and ensure that all the files are restored

11. Use the Log Shipping Monitor and check the last_loaded_file is the latest Tlog backup (identified in Step 9) taken from the Primary Database.

12. Confirm no one is connected on the database by using the following command on Secondary Server Instance

Use master
GO
select * from sysprocesses where dbid = DB_ID('Northwind’)

13. Only after above confirmation run the sp_change_secondary_role on the Secondary database to bring it online.

USE master
GO

EXEC msdb.dbo.sp_change_secondary_role
@db_name = 'Northwind',
@do_load = 0,
@force_load = 0,
@final_state = 1,
@access_level = 1,
@terminate = 1,
@keep_replication = 0,
@stopat = null

Please Note: The above step is very important and if we get any error in the above step don’t move ahead until the error is fixed.

If you error with sp_verify_jobs that can be ignored and that error is expected to come.

14. From the Enterprise Manager verify that the database is opened on the Secondary Server in Read/Write Mode.

15. On the Monitor SQL Instance fire the following statement

USE master
GO
EXEC msdb.dbo.sp_change_monitor_role
@primary_server = 'Primary' ,  -- Old Primary server /New Secondary Server
@secondary_server = 'Secondary', --Old Secondary Server/New Primary Server
@database = 'Northwind',
@new_source = '\SecondaryLogShipping'  --“New share for T-Logs on the New Primary Server”

16. Use the Database Maintenance Wizard on the new primary server and you will see the the Maintenance Plan for Log Shipping. Go to the Properties of the Log shipping Plan and click on the Log Shipping and add the old primary server as the destination server.

The above step will create Copy and Restore Jobs on the Old Primary Server/New Secondary Server.

17. Verify that the Copy and Restore Jobs are created on New Secondary Server.

18. Disable all the Log Shipping Jobs viz Backup Job on the New Primary server and Copy and Restore Job on the secondary Server

19. Archive all the TRN files from the Log Shipping share folder from previous Log Shipping which were created before the Failover on both servers into some different folder. This will clean up the folder.

20. Check the basic log shipping is working fine by manually

a. Manually run backup job first and verify that backup TRN is created

b. Manually run copy job and see whether all the TRN Files are copied.

c. Manually run the restore job and verify that file is restored

d. Use the Log Shipping Monitor on the Monitor server to verify that all TRN file backup,copied and loaded file are same.

21. If the above Test is successful, Enable all the Log Shipping Jobs.

22. Open the database on Old Secondary Server/New Primary Server for users and start Application for end users.

23. Ensure that Logs Shipping is going fine from Log Shipping Monitor

24. For the Failback repeat all the above steps starting Step 1 (Skip step 1).

Rollback Strategy

1. Restore the Full Database Backup captured on the Primary Server.

2. Restore the Full Database Backup WITH STANBY option on the secondary site from the tapes which was send to Secondary site  and apply all the Tlog generated until step 10

3. Reconfigure Log Shipping

 

Parikshit Savjani
Premier Field Engineer, Microsoft