Sql-server – AG (SQLServer 2014) – Suspend Data Movement

availability-groupssql serversql server 2014

I have a primary replica and one secondary. Multiple availability databases. Secondary replica is located at DR site.

For DR testing, is it possible to failover to the secondary replica, make (test) data changes on the secondary (now primary) replica and then failback but without moving back the test data?

I was thinking this may be possible through suspending data movement i.e.

  • Suspend data movement
  • Failover to secondary (DR server) replica
  • Insert test data
  • Failback to primary without synchronizing test data
  • Resume data movement

Best Answer

The short answer is No you cannot do that. First, if you suspend the data movement in one of the availability group database, you no longer able to failover it to your secondary node. Unless you make a forced failover. If you want to suspend the data movement in one of your databases, and also want to make the failover, you have to remove that specific database from the availability group and change the data. However, to add it back to the availability group, first you need to synchronize it back again.