SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DR Testing Question


DR Testing Question

Author
Message
SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2862 Visits: 3075
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.
Andrew G
Andrew G
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3921 Visits: 2279
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.
SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2862 Visits: 3075
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.
SQL!$@w$0ME
SQL!$@w$0ME
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2250 Visits: 1258
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
Andrew G
Andrew G
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3921 Visits: 2279
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.
SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2862 Visits: 3075
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.
SQLAssAS
SQLAssAS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 1283
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.
SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2862 Visits: 3075
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search