Log Shipping failover when Primary is not accessible

Question

Log Shipping Fail-over

I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible. My Steps for fail-over will be

1. Copy and restore T-logs (WITH RECOVERY) that are not applied, to put the database online
2. Since the Primary in not accessible so I cannot take a T-log and put it in restoring state
3. Disable the above two jobs
4. Change the application connection strings to the new primary.

My confusion arises what should I do when the Old Primary comes live again:

1. Should I take the T-log back up and put the database in restoring state so that I can set it up as a secondary and then fail-over to make it primary.

If that’s the case, then won't the log shipping fail if I want to configure it without initializing (since I have the database in restoring state) as it will look for the last T-log that I have not applied to my new secondary, as the primary was not accessible during fail-over?

Answer 1

The issues and complications you mention are ones I've wrestled with myself. Quite a while back, I did my best to script out a solution to help make it easier to fail over and also to fail back. I would suggest going to codeproject.com and searching for "Streamline Log Shipping Failovers" to look at the quick article I wrote there. You can download the SQL script and see if it's helpful for your situation. Essentially, it is a SQL script that you run on the Secondary and it dynamically generates the scripts you need to fail over and later fail back.

I have used it many times in a Production environment to perform DR tests. I have also used it once in a true Production down situation and it really helps streamline the process.