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.
- Download and open the zip file (link above) and then open the SQL_DR_Master.sql file in SQL Management Studio.
- 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.
- 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.
- 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.
