SQL Server 2014 – AlwaysOn Disaster Recovery Clarifications

availability-groupshigh-availabilitysql server 2014

Whenever I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node and test the connectivity and if everything is okay. I have two questions.

1)How to revert back the AOAGs to original Synchronous mode — Should I Perform a Forced Manual Failover of an Availability Group to PRODUCTION Servers(Sync) from DR/BCP (Asynchronous) node ?

2)We have one extra BCP node MSDTC and AOAGs Listener. Should I turn then online before I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node ?

Please correct me. I am asking these questions for BCP site Testing plan view.

Best Answer

1)How to revert back the AOAGs to original Synchronous mode -- Should I Perform a Forced Manual Failover of an Availability Group to PRODUCTION Servers(Sync) from DR/BCP (Asynchronous) node ?

You've failed over to asynchronous nodes. This means all of the database flows are paused and there is no current way (assuming it's a true disaster) how far behind your secondary replicas were. Now that they've come back up, we know that it isn't going to be 100% the same data (they are asynchronous). This was not mentioned in the question but I'm going to add it to the answer as it's extremely important as it's part of your SLA.

  1. Take a database backup of the original primary databases. To do this you will need to kick them out of the AG. Alternatively you could take a database snapshot (inside of SQL, not SAN).
  2. Resume data movement between the replicas at the database level.
  3. Set the local primary replica to synchronous and a remote replica to synchronous.
  4. Wait for them both to say 'Synchronized'. They will start out as 'Synchronizing'. This could take quite a while depending on multiple factors such as downtime, data generated, IO, Networking, etc.
  5. Once Synchronized, find a good time to take a minute or less outage. Use that time to do a planned manual failover.
  6. Set the local servers to be synchronous to each other.
  7. Remove synchronous from the remote replica and set it back to async.

2)We have one extra BCP node MSDTC and AOAGs Listener. Should I turn then online before I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node ?

How do you have an "extra AOAG listener" just chilling out? I don't understand that part of your question.

MSDTC is for distributed transactions, which aren't supported in 2012/2014 and only supported with certain restrictions (as of this moment) in 2016. Thus this is not required in a supported scenario. If you're going unsupported then this is still a moot point as the local MSDTC will be used.

Unless I'm missing something (completely possible) or not understanding, this is not needed.

Related Question