Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Helping Streamline Log Shipping Failovers

By Micah Nikkel,

Introduction

Similar to a lot of DBAs, I find that there just isn't enough time in a day to get everything done.  Try as you might, new issues continue to introduce themselves and old tasks re-emerge needing more attention than you first anticipated.  Then, your boss drops by to ask when you're going to perform that much-needed Disaster Recovery (DR) test on the Production SQL Servers. 

This is difficult, as there really is no clear answer  to the question in front of you.  Would your boss like you to do the things that are important to your end customer?  "Yes." you say.  After all, who wouldn't?  Ok… but would your boss also like you to ensure that the company's critical data is being protected from substantial down-time, and actually prove that it is?  "Yes..." you say again.  This is about as far as a lot of people get before they start struggling with how they're going to find time to scrape together the steps for a DR test.

Some Assistance

I want to introduce you to what is hopefully a big assist in trying to get that DR test done and being ready for a true DR situation.  An easier way to solve your issue above does exist, which is to do yourself a favor and don't start from scratch.  Let's walk through a solution that aims at trying to help streamline the process of failing over to a Log Shipping Standby server and back again.  You can then go about using it as is or updating it to better suit your needs.

Let's get started...

Editor's Note: The most current version of the script is now here: http://streamlinelogshippingfailovers.codeplex.com/

Use SQL Management Studio to open up the master script provided with this article called SQL_DR_Master.sql.  Scroll down past the declarations of variables to the  three variables that must be set in order to execute the script.  The first variable, @FailOverFromPRIMARY, controls whether to attempt to fail over to from the PRIMARY or not .  If this variable is set to 'Y', a final transaction log backup will be attempted on the PRIMARY.  Then, all the available transaction log backups will be applied to the SECONDARY to bring the system up to the latest point possible before making the databases available.  If set to 'N', the solution will simply bring up the SECONDARY after trying to apply any available transaction logs first. 

Next, update the @ScriptsLocation to a network location where you want the failover scripts to be created.  An example would be '\\MyServer\MyFailoverFolder'.  The scripts produced will then be created in the folder specified, which should be a safe and accessible place. 

Lastly, update the @RunType to either 'Manual' or 'Automatic' to determine how Step 4 will be executed.  If 'Manual' is chosen, a set of statements will be created and displayed for review, after which they need to be copied to a New Query and run.  If 'Automatic' is chosen instead , Step 4 will take those same statements and run them automatically.  For the sake of learning or when running it the first time, choosing 'Manual' might be best as you can then see what commands will  be run. 

Executing the Failover

Below is a table describing the main actions each script will take, along with the desired outcomes.  The first 5 scripts fail over from the PRIMARY to the SECONDARY and the last 5 scripts fail back from the SECONDARY to the PRIMARY.  Review the table below and take the time to go over the scripts it outputs to ensure things make sense to you and you don't foresee any issues.  After confirming things look good with the scripts produced, you are ready to begin with the first step of failing over your server.

Script

Target Server

Primary Action

Expected Outcome

01.sql

PRIMARY

Creates the directory that will hold the scripts produced, and disables LS backup jobs.

New folder now available in the network location specified and LS Backup jobs disabled.

02.sql

SECONDARY

Removes the LS restore delay, applies all logs, and disables LS restore jobs.

SECONDARY's databases have had all currently available logs applied.

03.sql

PRIMARY

Performs log backups.

All databases are in NORECOVERY mode, waiting for failing back

04.sql

SECONDARY

Generates list of any remaining logs to apply to databases and restores them.

All of SECONDARY's databases have had all logs applied.

Databases remain inaccessible.

05.sql

SECONDARY

Executes restore command on each database.

All of SECONDARY databases are available and ready for use.

06.sql

SECONDARY

Performs log backups.

All databases are in NORECOVERY mode, waiting for LS to reengage

07.sql

PRIMARY

Restores available log backups.

All of PRIMARY's databases have had all currently available logs applied.  Databases have limited accessibility.

08.sql

PRIMARY

Sets all databases back to multi-user mode.

All databases are fully accessible.

09.sql

PRIMARY

Enables all LS backup jobs.

Log backups are being made again by the PRIMARY.

10.sql

SECONDARY

Enables all LS restore jobs.

Log backups are being restored again by the SECONDARY and Log Shipping is running again.

To actually begin the Failover, open 01.sql in SQL Management Studio, verify it's connected to the correct server, and click Execute.  Repeat this with 02.sql - 05.sql to fully complete the Failover.  Refer to the table above as you go to help ensure things are going as expected. 

After executing 05.sql,  you will now be running on the SECONDARY server.  You can then perform some tests on the SECONDARY (for example, temporarily pointing your application at it to ensure it works properly).  When finished, execute 06.sql through 10.sql to fail back to the PRIMARY (carries over any changes made on the SECONDARY).  Make sure to repoint your application back to the PRIMARY and do whatever testing is necessary to make certain things are as expected and completely ready for your users.

Summary

After you've successfully performed your failover test, remember to create any additional documentation around the use of this solution, if needed.  Also, don't forget to periodically perform other steps that are needed for a proper failover as well (copy over logins and jobs, etc.).  You and your boss can both rest a little easier now that you know you are better prepared for a true disaster.

Update: The script has been removed from this article, as it is out of date. The most current version of the script is maintained by Micah here: http://streamlinelogshippingfailovers.codeplex.com/

Total article views: 1136 | Views in the last 30 days: 1
 
Related Articles
FORUM

Backup FileGroup and Restore on Secondary Database

Backup FileGroup and Restore on Secondary Database with same filegroups

FORUM

Changing Role Between Primary and Secondary Servers in SQL Server 2005 Log Shipping:

Order of Failover and Role Switch between Primary and Secondary

FORUM

Initialize Secondary Database Tab Log Shipping

Initialize Secondary Database Tab (Third Option)

FORUM

How to merge SQL 2005 database files

primary and secondary database file

SCRIPT

Log Shipping - How you can convert the secondary database into primar

This topic explains the log shipping if the primary database is suspected or corrupted and how you c...

Tags
disaster recovery (dr)    
dr    
failover    
log shipping    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones