Log Shipping failover when Primary is not accessible

Question

Log Shipping Failover

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 failover will be

1) Copy and restore the unapplied t logs with Recovery 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 failover to make it primary.

If that’s the case then wont the log shipping fail if I want to configure without initializing(since I have the database in restoring state) as it will look for the last t log which I Have not applied to my new secondary as the primary was not accessible during failover.

Thanks in advance

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.