Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DR Testing Question Expand / Collapse
Author
Message
Posted Wednesday, July 03, 2013 5:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1470269
Posted Wednesday, July 03, 2013 11:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:40 PM
Points: 1,077, Visits: 1,496
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.

Post #1470305
Posted Monday, July 08, 2013 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1471335
Posted Monday, July 08, 2013 2:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:28 PM
Points: 49, Visits: 312
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
Post #1471365
Posted Monday, July 08, 2013 8:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:40 PM
Points: 1,077, Visits: 1,496
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.
Post #1471422
Posted Thursday, August 01, 2013 3:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1480165
Posted Thursday, August 15, 2013 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:49 AM
Points: 226, Visits: 667
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.
Post #1484797
Posted Thursday, August 15, 2013 11:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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.
Post #1484857
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse