0

We are planning to activate our secondary DC2 site and bring down our primary Data Centre DC1.

We have three node cluster with AG's ON. Two nodes SQL07P & SQL08P which are in automatic failover (Synchronous commit) in Primary Data Centre DC1 but third node SQL06P in Manual failover (Asynchronous commit) in Secondary Data Centre DC2 (for DR purpose).

We have three node clusters:

SQL version on three node cluster is SQL 2012 SP4 enterprise edition

SQL07P (Primary) -Automatic failover -> Synchronus Commit (DC1)

SQL08P(secondary) - Automatic failover -> Synchronus commit (DC1)

SQL06P (secondary) - Manual failover -> Asynchronus commit (DC2)

enter image description here

When primary DataCentre DC1 goes down including SQL07P &SQL08P server, How should I perform failover as SQL06P node in DC2, failover mode is in Manual mode?

I think I need to perform forced failover it with some data loss but how? -- please share me steps if possible?

  • Do we need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration?

  • Also, how should I reconfigure back SQL07P & SQL08P node in DC1 back in synchronized state in case of primary DC1 crashed?

  • Will it become synchronized again once I perform forced failover?

Please help me out.

Regards,

Ken

1

I've done this with SQL Server 2014 between two local data centers (within a few blocks of each other), so mileage may vary given that you're using SQL Server 2012:

  1. You shouldn't need to force a quorum. Since the failover is going to be a manual one, the quorum is already decided by you performing the activity. If the nodes go down and then recover, the quorum should be reestablished in Windows Failover Cluster Manager. Because your SQL06P node is asynchronous, any failover to it will result in some data loss depending on how active this database environment is.

  2. If there's a failure on your two nodes in DC1, and you fail-over to DC2 without issue, then DC1's nodes (once recovered) should begin the synchronization process and catch back up. Otherwise, you will need to do log file restores from your DC2 node onto your DC1 nodes. In most DR scenarios though, running Asynchronous at your DR site is recommended due to latency.

  3. Yes, because they are configured for Synchronous Availability. If you have a test environment you can perform some failover testing in and see what occurs in your network, that would give you a good idea of the impact. It's tough to give you specifics since each network is different, the location of servers is different, etc. Anything you can do to test without touching your production environment is recommended.

For doing the failover, you should only need to engage it through the SQL Server Management Studio GUI:

  • Expand AlwaysOn High Availability -> Availability Groups
  • Right click on your Availability Group and select Failover
  • Select the node you want to failover to
  • Confirm the warnings about Asynchronous replication
  • Connect to the other node that has permissions to execute a failover
  • Confirm settings and process are correct
  • Select OK and wait
  • Perform checks and validations via your applications, monitoring, logs, etc. that all is working as you expect it.

Some additional reading on the Availability Modes: Differences between availability modes for an Always On availability group - Asynchronous-Commit Availability Mode

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.