Categories
High Availability

Role Change in SQL 2005 Log Shipping

In SQL 2000 where the role change was relatively easy through the use of stored procedures, unfortunately in SQL 2005 those SPs are no longer used and we have to perform the following steps to sync the secondary server with primary and then failover.

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

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

Step 3: 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 4: 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 DBName 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 DBName with Recovery

The above command will bring the original secondary online from Read-only mode.

Now you original secondary has become primary server, now you can repoint your applications to this server.After sometime if your original primary server is up and running fine,you can see that the original primary db will be in restoring state (becoz of Step 3) and hence tran logs can be applied from the new primary.

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

Step 6: On your original secondary (the new primary), reconfigure log shipping by including the below steps,

* Use the same share for creating backups that you created for the original primary server.

* When adding the secondary database, in the Secondary Database Settings dialog box, enter the name of the original primary database in the Secondary database box.

* In the Secondary Database Settings dialog box, select No, the secondary database is initialized because it is already in restoring state and so tran logs can be applied.

Step 7: 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).

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*