DR Testing Question

  • Hi Guys,

    I am currently in the planning process of a DR testing(Mirroring Failover) and I need some clarification for couple of the steps that I need to provide.

    Here is my scenario......we will be failing over a mirror database from Prod to DR and application team will be doing some testing on the DR side after I failover, however, when I failback to Prod, we do not want to carry over the testing changes app team made on the DR side. So, I was thinking to do the following steps to accomplish that but I need to confirm if there is any other better work around.

    -Backup the prod database before failover.

    -Failover the database from Prod to DR

    -Once the failover is done and all mirroring is synchronized, I will remove mirroring.

    -Application team start their testing at this point.

    -During this time I will copy the backup file that I took earlier to DR server.

    -Once the testing is done, I will restore the DR database with the Prod backup

    -Then setup mirroring between DR and Prod

    -Now, failback the database from DR side to Prod and we will have the same data on both DR and Prod as before the failover.

    Does these steps seem correct? Can you guys think of a better way to accomplish this? Please advise.

    Thanks,

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • You don't need to fail-over at all, just break mirroring, then RESTORE WITH RECOVERY on the mirror to make it useable. Then recreate the mirror after the testing.

    Make sure you disable any automated log backups on prod too, or you'll need to restore those when you recreate the mirror.

  • Andrew G (7/3/2013)


    You don't need to fail-over at all, just break mirroring, then RESTORE WITH RECOVERY on the mirror to make it useable. Then recreate the mirror after the testing.

    Make sure you disable any automated log backups on prod too, or you'll need to restore those when you recreate the mirror.

    Thanks Andrew, but they do want to go through that failover process as if it's a real disaster......So, I can't really follow your steps.

    Any other suggestions?

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • I agree with all the steps SueTons, except after a role switch, jobs that ran on the former principal database must be disabled and recreated on the new principal server(DR) to run there(if needed) and the logins of users or processes that need to connect to the database should be created in Mirror(Fix Orphaned users). When the former primary/principal server instance is available after testing, you should delete/disable the original jobs in DR.

    You will need a log backup from Prod to reconfigure mirroring. These are my assumptions

  • Seems ok as long as you take into account that the original primary database will not be in a recovered state after you break mirroring.

  • Andrew G (7/3/2013)


    You don't need to fail-over at all, just break mirroring, then RESTORE WITH RECOVERY on the mirror to make it useable. Then recreate the mirror after the testing.

    Make sure you disable any automated log backups on prod too, or you'll need to restore those when you recreate the mirror.

    Andrew, Quick question.....if I follow your steps and restore the mirror databases WITH RECOVERY, we still need to change the connection string to point to the new server?....how do you go about doing that?

    Regards,
    SQLisAwe5oMe.

  • That depends on your app and how it connects to the database? You should set up a DNS for it, then in a DR scenario you just need to switch the DNS in the background - then no connection strings need to be changed.

  • SQLSteve (8/15/2013)


    That depends on your app and how it connects to the database? You should set up a DNS for it, then in a DR scenario you just need to switch the DNS in the background - then no connection strings need to be changed.

    Thanks, this will be taken care by application team.

    Regards,
    SQLisAwe5oMe.

Viewing 8 posts - 1 through 7 (of 7 total)

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