Sunday, March 4, 2012

Role Change In Log Shipping

Log Shipping Role change In SQL Server! 
In SQL Server 2000 role change is pretty much easy through using of stored procedures, but unfortunately in SQL Server 2005 and later versions those SP's are no longer used and we have to do the following steps to sync the secondary server with primary and then fail over. 

Step 1: Copy all uncopied backup files from the backup share of original primary server to the copy destination folder of original secondary server. 

Step 2:  Run this below script to finding what was the last restored LSN in Secondary Database. 

Select redo_start_lsn 
from sys.master_files
where
database_id = db_id('SecondaryDatabaseName')
and type = 0 

Step 3: Apply any unapplied transaction log backups which were copied in step1 sequentially in the secondary database. 

Step 4: If the original primary server instance is not damaged and if it is still accessible, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database(i.e original secondary server). 

Step 5: After the secondary servers are synchronized, you can fail over to the secondary by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online.Execute the below command. 

To bring the original secondary db online with the last tran log copied into secondary server. 

Restore log Databasename
from disk='Path of the last tran log copied into secondary'
with Recovery 

If Step 3 is not possible i.e if you cannot access the original primary server just execute. 

To bring the original secondary db online if no tran logs are available.

Restore database DadabaseName
with Recovery 

The above command will bring the original secondary online from Read-only mode. Now your original secondary has become primary server, now you can repoint your applications to this server. 

Step 6: Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server. 

Step 7: On your original secondary (the new primary), reconfigure log shipping by using same share for creating backups. 

Step 8: Enable the log shipping backup job on the secondary server (the new primary server), and the copy and restore jobs on the primary server (the new secondary server). 

Check these below articles as well for related to Log Shipping. 

Prerequisites for Log Shipping In SQL Server 2008
Role Change in SQL 2005 Log Shipping, Role Change in SQL 2008 Log Shipping, Role Change in SQL 2008 R2 Log Shipping, Role Change in SQL 2012 Log Shipping, Role Change in SQL 2000 Log Shipping, SQL 2005 Log shipping roll change where secondary is readonly state, SQL 2008 Log shipping roll change where secondary is readonly state, Role change using Log shipping, Performing a Log Shipping Role Change, log shipping role change, How to rebuild logshipping after role change, How To Rebuild Logshipping After Role Change, Problem on Log Shipping role change, changing monitor role and resolve login in log shipping, Changing Log Shipping Role, How to set up and perform a log shipping role change, role reversal in logshipping, Log shipping role change, Role change with log shipping doesn't work, Role change failed, Log Shipping vs database mirroring, LogShipping Change Secondary Role, Logshipping role change

0 comments:

Post a Comment