Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2016

Streamline Log Shipping Failovers

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
29 Aug 2018MIT5 min read 34.5K   543   11   10
SQL script that dynamically generates the DR scripts for failing over/back all Log Shipped databases. While it makes even a single database failover/failback a more streamlined process, it's most helpful for servers with multiple databases, such as SharePoint, consolidated SQL Servers, etc.

Introduction

Reasons You Might Want to Streamline a Log Shipping Failover

  • Perform a Disaster Recovery test and demonstrate you are prepared for a true disaster.
  • Bring up the Secondary for service because the Primary is down and won't come back up.
  • Migrate to a new server with minimal downtime.

Getting Started

To begin, let's quickly customize the master script for your environment so it can generate the needed failover scripts.

  1. Download and open the zip file (link above) and then open the SQL_DR_Master.sql file in SQL Management Studio.
  2. Update the variables found after the variable declarations.
    • @FailOverFromPRIMARY
      • Should an attempt be made to fail over from the PRIMARY or not? If set to 'Y', a final transaction log backup will be executed on the PRIMARY before applying all available transaction logs to the SECONDARY. If set to 'N', the SECONDARY will not attempt the backup from the PRIMARY before applying all available logs.
    • @ScriptsLocation 
      • Sets the existing network location for where the failover scripts should be created. For example, '\\MyServer\MyFailoverFolder', which should be a safe and accessible place.
    • @RunType 
      • Defaults to 'Automatic'. This tells script 04.sql to apply transaction logs automatically and then just show the statements that were run. This should not be changed unless trying to troubleshoot an issue or desiring to better understand what's taking place.
  3. Review the Considerations section for prerequisites needed before running the script. For example:
    • Select 'Query -> Results To -> Results To Grid' in SQL Management Studio.
    • Ensure a LinkedServer from Secondary to Primary exists.
    • Verify the variables mentioned earlier are set correctly.
    • Etc.
  4. Execute the script against the SECONDARY. This does NOT actually begin the failover process. It simply generates the scripts that will be used to perform the failover.
    • Safe to run anytime on the SECONDARY server for review of scripts / steps produced.
    • If actually failing over, run as part of that overall process to ensure the generated scripts are as current as possible and properly reflect the current environment.

Executing the Failover

Below is a table describing the main actions each generated 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 (if desired).

To begin the Failover, open 01.sql - 05.sql in SQL Management Studio. Review the tables below and take the time to go over the generated .sql scripts 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.

Verify 01.sql is connected to the correct server, and click Execute. Repeat this with 02.sql - 05.sql to fully perform the Failover. Refer to the table below as you go to help ensure things are going as expected.

Steps For Failover:

Script Target Server Primary Action Expected Outcome
01.sql PRIMARY Executes and then disables LS backup jobs. LS Backup jobs have been run and are now disabled.
02.sql SECONDARY Removes any LS restore delay, applies all logs, and disables LS restore jobs. Databases have had all available logs applied.
03.sql PRIMARY Performs log backups. Databases are in NORECOVERY mode and ready for failing back.
04.sql SECONDARY Generates list of any remaining logs to apply to databases and restores them. Databases have had all logs applied and remain inaccessible.
05.sql SECONDARY Executes restore command on each database. Databases are now available and ready for use.

After executing 05.sql, you will be running on the SECONDARY server. If this is a DR test, you can perform some tests on the SECONDARY (for example, temporarily pointing your application at it to ensure it works properly).

To perform a Failback, open 06.sql - 10.sql in SQL Management Studio. Verify 06.sql is connected to the correct server and click Execute. Repeat this with 07.sql - 10.sql to fully complete the Failback. Refer to the table below as you go to help ensure things are going as expected.

Steps For Failback:

Script Target Server Primary Action Expected Outcome
06.sql SECONDARY Performs log backups. Databases are in NORECOVERY mode, waiting for LS to reengage.
07.sql PRIMARY Restores available log backups. Databases have had all available logs applied and are still in NORECOVERY mode.
08.sql PRIMARY Sets all databases back to multi-user mode. Databases are now available and ready for use.
09.sql PRIMARY Enables all LS backup jobs. Log backups are being made by the PRIMARY.
10.sql SECONDARY Enables all LS restore jobs. Log backups are being restored by the SECONDARY and Log Shipping is operating normally.

Please note that any changes made on the SECONDARY during this window will be carried back over to the PRIMARY in order to keep the databases in sync. This allows Log Shipping to pick back up right where it left off. 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.

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 successful failover (copy over logins and jobs, etc.).

Summary

This solution is for helping perform Log Shipping failovers in a more systematic and repeatable way. This allows for it to scale from one database to many and promote a better understanding of how Log Shipping works.  It also allows for troubleshooting errors more easily than with a highly automated solution.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionTransaction Log Shipping Report on Old Primary Pin
Member 1305450812-Mar-17 9:48
Member 1305450812-Mar-17 9:48 
AnswerRe: Transaction Log Shipping Report on Old Primary Pin
Micah Nikkel27-Mar-17 3:34
Micah Nikkel27-Mar-17 3:34 
QuestionJust a suggestion Pin
alago19722-Oct-15 8:59
alago19722-Oct-15 8:59 
Hi Micah,

Great script, really. Thanks for sharing!!

Just one suggestion (for a small quirk I encountered). I would add a comment in the source to warn the script user to properly edit the @Execute_ScriptToExport variable, adding the server name to BCP via (por example) "-S server\instance name". I've left out unedited in my case and the error popped after some head-scratching Smile | :)

something like :

SET @Execute_ScriptToExport = 'BCP ' + QUOTENAME('SELECT SQLToRun FROM Tempdb.dbo.LS_Step01', '"') + ' QUERYOUT ' + @ScriptsLocation + '\Streamline_LS_Failovers_' + @FormattedDateTime + '\01.sql -S <server_or_instance_name> -T -c'

Regards,

Alvaro
AnswerRe: Just a suggestion Pin
Micah Nikkel25-Oct-15 15:01
Micah Nikkel25-Oct-15 15:01 
QuestionScript error when servername includes - Pin
Member 114396329-Feb-15 9:00
Member 114396329-Feb-15 9:00 
AnswerRe: Script error when servername includes - Pin
Micah Nikkel10-Feb-15 7:30
Micah Nikkel10-Feb-15 7:30 
SuggestionUsing Log Shipping To Migrate To New Server Pin
Micah Nikkel3-Sep-14 4:06
Micah Nikkel3-Sep-14 4:06 
Questionquery completed with errors and no scripts are generated Pin
Member 110483991-Sep-14 7:01
Member 110483991-Sep-14 7:01 
AnswerRe: query completed with errors and no scripts are generated Pin
Micah Nikkel1-Sep-14 11:36
Micah Nikkel1-Sep-14 11:36 
GeneralRe: query completed with errors and no scripts are generated Pin
Micah Nikkel3-Sep-14 4:10
Micah Nikkel3-Sep-14 4:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.