Are any of the following true for you? Would you like to...
- Perform a DR test to demonstrate you are prepared for a disaster.
- Bring up the Secondary because the Primary is down.
- Migrate to a new server with minimal downtime.
If so, let's get to it...
[Click here to sign up for notifications of code updates. All code updates will be made available on both sites.]
- Unzip the file associated with this article and open SQL_DR_Master.sql in SQL Management Studio.
- Update the three variables found after the variable declarations.
@FailOverFromPRIMARY - controls whether to attempt to fail over from the PRIMARY or not. If set to '
Y', a final transaction log backup will be attempted on the PRIMARY before applying all available transaction logs. 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 where the failover scripts will be created. For example, '\\MyServer\MyFailoverFolder', which should be a safe and accessible place.
@RunType - defaults to '
Automatic', which tells script 04.sql to apply transaction logs automatically and then show the statements that were run.
- Review the Considerations section within the script. For example:
- Select 'Query -> Results To -> Results To Grid' in SQL Management Studio.
- Ensure a LinkedServer from Secondary to Primary exists (if failing over).
- Verify the variables mentioned up above are set correctly.
- 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 process and not before as the generated scripts would be out of sync with 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. 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.
||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.
||Removes the LS restore delay, applies all logs, and disables LS restore jobs.
||SECONDARY's databases have had all currently available logs applied.
||Performs log backups.
||All databases are in NORECOVERY mode, waiting for failing back
||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.
||Executes restore command on each database.
||All of SECONDARY databases are available and ready for use.
||Performs log backups.
||All databases are in NORECOVERY mode, waiting for LS to reengage
||Restores available log backups.
||All of PRIMARY's databases have had all currently available logs applied. Databases have limited accessibility.
||Sets all databases back to multi-user mode.
||All databases are fully accessible.
||Enables all LS backup jobs.
||Log backups are being made again by the PRIMARY.
||Enables all LS restore jobs.
||Log backups are being restored again by the SECONDARY and Log Shipping is running again.
To 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. 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). 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.
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.).