Helping Streamline Log Shipping FailoversHelping Streamline Log Shipping Failovers

Like 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, along comes your boss to ask when you're going to perform that much-needed disaster recovery (DR) test on the production SQL Servers. This is a tough situation to be in, as you're between a rock and a hard place. 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 shaking their head wondering how they're going to find time to scrape together the code for performing 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 not re-invent the wheel.  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, within the guidelines that exist in the solution, go about using it as is or updating it to better suit your needs.

Let's Get Started: Overview of Scripts Produced

-- Creates Script 01 to run against the PRIMARY and saves it to the designated folder.

-- Enables xp_cmdshell temporarily to help run the remainder of this solution.

--; Generates some necessary constructs (temp tables, etc.) for later use in the script.

--> Creates a directory for the Failover scripts to be stored in for later use.

-- Creates Script 02 to run against the SECONDARY and saves it to the designated folder.

--; Updates the restore delay to 0 for all database jobs.

-- Executes all active Restore Log jobs and then disables them, saving a list for enabling them again later.

-- Creates Script 03 to run against the PRIMARY and saves it to the designated folder.

-- Kills any active connections (except yours) to all the log shipped databases.

-- Performs final log backup for each database (to be later applied to SECONDARY).

-- Places each database into NO_RECOVERY mode so they cannot be accessed and are ready for failback at any time.

-- Creates Script 04 to run on the SECONDARY and saves it to the designated folder.

-- Reads each database's backup location and creates a list of files from each directory.

--> Restores all available logs on the SECONDARY's databases so they can be enabled for use.

-- Creates Script 05 to run against the SECONDARY and saves it to the designated folder.

-- Restores appropriate log backups for the databases so they are now accessible for use.

-- This completes failing over to the SECONDARY.  The SECONDARY can then be used until it is time to fail back.

-- PLEASE NOTE: if failing over for a significant amount of time, an experienced DBA must be involved to ensure log backups are properly configured and running on the SECONDARY, etc.

-- The second set of 5 scripts fail back to the PRIMARY, taking any changes made on the SECONDARY with them.

-- Creates Script 06 to run against the SECONDARY and saves it to the designated folder.

-- Backs up any database changes performed on the SECONDARY in order to apply back to the PRIMARY.  Leaves databases in NO_RECOVERY mode so they can begin receiving logs in the log shipping configuration.

-- Creates Script 07 to run against the PRIMARY and saves it to the designated folder.

-- Applies changes mentioned above on PRIMARY in order to bring the PRIMARY and SECONDARY databases back into sync.

-- Creates Script 08 to run against the PRIMARY and saves it to the designated folder.

-- Enables each database so they're now accessible for use again.

-- Creates Script 09 to run against the PRIMARY and saves it to the designated folder.

-- Enables all previously active log backup jobs.

-- Creates Script 10 to run against the SECONDARY and saves it to the designated folder.

-- Enables all previously active log restore jobs.

-- Ensures xp_cmdshell is set to the value it was at before this process began.

Once you've updated the variables and run the master script, open the resulting files in SQL Management Studio. Take the time to go over the outputted files 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. Also, it should go without saying, but running this overall solution in a test environment is highly recommended before attempting a true Production DR test.

Executing the Failover

To begin the failover, open the 01.sql tab in SQL Management Studio, verify the connection is correct, as specified in the script itself, and then execute it.  With that, you have now begun the process of failing over to the Secondary server!  Execute 02.sql through 05.sql and you will now be running on the Secondary server.  After performing some tests on the Secondary (for example, pointing your application at the Secondary to ensure it works properly), execute 06.sql through 10.sql to fail back to the Primary (carries over any changes made on the Secondary).  Note that you may also set a variable that ignores the Primary and simply applies logs to the Secondary and brings it up.  This is meant to be used if the Primary is not available for failover purposes.

Summary

With that, you've successfully performed your failover test. Remember to create proper 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 (periodically copy over logins and jobs, etc.). Congratulations on the successful test and the fact that you can now tell your boss that you are better prepared for a true disaster.

Download the Code

Here's the link for downloading the master script described in this article: https://www.dropbox.com/s/0hiyhdbeoyjsb8g/SQL_DR_Master.sql