Post reply

SQL 2005 logshipping failover DR test and fail back

  • November 12, 2021 at 2:26 pm

    #3952413

    Hi all,

    There is a planned DR test for one database which has customized log shipping, same as backup, copy and restore job.

    I need some steps to failover and fail back to old primary to test DR. Can you share your experience.

  • November 12, 2021 at 3:23 pm

    #3952439

    What do you mean by "customized" log shipping?

    Are you trying to do a timed drill to estimate recovery time, or just trying to prove you can failover successfully?

    You ask for steps to failover and fail back.  That implies you haven't done this before, correct?  If so, read up. But one database shouldn't be bad.

    Are you going to have to change IP addresses or DNS names on the servers when you swap? Do you have other dependencies like transaction replication from/to the primary server? Do you have services that hammer the server continuously that might need to be stopped/blocked?

    Practice in a test environment before the real test.

    Do you already have the process planned and documented?  If not, do so. I recommend your documentatin start with criteria and guidelines for determining that you actually need to failover (things can go temporarily wrong that might be fixable quickly), and can (what if the secondary server is unresponsive/unreachable?) fail over.

    If a timed test, have a knowledgeable team member observing, taking notes, & providing feedback/course corrections if needed. If simply a proof-of-concept with no concern about elapsed time, the person doing failover ccould take notes, thought I'd still recommend a skilled observer. Maybe use a screen recorder to track everything you do for playback later.

    We did a log-shipping failover test with multiple (~ 10?) related databases in the cloud and dozens of services & applications dependent on fixed IP/DNS address a few years ago... With observers including untrained QA observers and attempts to have backup database personnel not familiar with log shipping participate for training (I don't recommend mixing the two - training and drills are very different and should be separated).

     

  • November 12, 2021 at 3:49 pm

    #3952445

    Hi,

    Thanks for your response. It is a simple DR drill, just for one database and there is no transactional replication etc.

    It is not a GUI based log shipping. It is a script based scheduled job by pervious DBA.

    It is just for one database. I am going to create one test database for me to do test.

    My plan:

    1.Disable all the job

    2.Take a tail log backup and apply it in secondary

    3.restore with recovery and bring secondary to primary

    4.Make DNS changes and connect applications, test everything

    Fail back, i am not sure. I hope i need to repeat the 4 steps

     

    https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server?view=sql-server-ver15

     

  • November 12, 2021 at 4:09 pm

    #3952454

    I think that would work and is kind of what the log shipping does anyway.  Failback would be just running the steps again but on the opposite server to where you originally ran them.

    • This reply was modified 2 years, 10 months ago by  DNA_DBA.
    • This reply was modified 2 years, 10 months ago by  DNA_DBA.

  • November 15, 2021 at 5:02 am

    #3953009

    Thank you, I will try test it out.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply