Sql-server – Sql Server Mirroring Failover and Failback

mirroringsql server

I have the following mirroring setup for DR.

Principal (ServerA)
Mirror    (ServerB).

When we do a DR test we do manual failover from Principal (ServerA) to Mirror(ServerB), so now:

ServerB is Prinicpal  
ServerA is Mirror

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror).

Is there any way to do this without breaking the mirror?

Finally it will go back to the original setup after the test, where Server A is Principal and Server B will be its mirror.

Best Answer

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror).

Is there any way to do this without breaking the mirror?

No this is not possible to prevent changes to ServerA. You have to resetup mirroring from original primary ServerA to ServerB

What you can do is :

  • Failover ServerA to ServerB.
  • Ask Users to test (making changes to serverB).
  • When the test is going on, in the background make full backup and transfer it to serverB. Make sure you use compression and instant file initialization. Depending on the database size, this will take some time.
  • Once the testing is done, you can point your application to serverA. In the background, restore the database on serverB WITH NORECOVERY. This is initializing the mirroring from backup.
  • Take transaction log backup on primary (serverA) and then restore it on mirror database (serverB).
Related Question