1

Set-up: Prod-HA-DR - SQL 2012 SP2 Prod-HA Synch, DR Asynch

Databases are distributed on Prod and HA equally like Active-Active Instances.

PROD-HA in Datacenter A DR - In Datacenter B

Scenario: This Saturday we broke the VPN between Prod-HA and DR datacenters to create the REAL DR

Scenario.

Action performed ones there was no connectivity between Prod and DR:

  1. Logged-in to DR SQL Instance
  2. Stopped the Clustered Service
  3. Start the Cluster Service with Forced Quorum
  4. Set the DR Node Weight to 1

These 4 steps completed successfully and DR node weight was showing 1 and PROD-HA node weight also 1 in clus admin on DR.

  1. But AG Force fail over with Data loss command

     ALTER AVAILABILITY GROUP [' +  @availabilityGroup + ']   FORCE_FAILOVER_ALLOW_DATA_LOSS;
    

gave below error:

ERROR Message:

The availability replica for availability group 'AG_XXXX' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group, or the WSFC cluster was started in Force Quorum mode. If the cluster was started in Force Quorum mode or the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.

  1. DR Database showed status Not Synchrinizing/Recovery Pending.

  2. Another aspect was that many SQL instances with AGs just worked fine with exactly same steps VS many instances gave this error and we could not failover to DR.

It important to have a successful DR test with AGs for us.Appreciate a quick response.

  • So you have FCI at PR and the DR node is standalone ? Or is it like you have all 3 standalone nodes on WSFC ? Do you have quorum setup ? – Shanky Jun 14 '17 at 16:34
  • Was this planned maintenance? If so, those aren't the steps to perform. Instead, you set the DR replica to sync mode, wait for it to catch up, then failover to it. Next switch quorum and votes if needed. Done. No stopping of services. No data loss. – Tara Kizer Jun 14 '17 at 16:43
  • It is planned but trying to mimic not planned This Saturday we broke the VPN between Prod-HA and DR datacenters to create the REAL DR. – SqlWorldWide Jun 14 '17 at 16:46
  • Those aren't the steps for an unplanned failover either though based on the documentation: docs.microsoft.com/en-us/sql/database-engine/… – Tara Kizer Jun 14 '17 at 16:53
  • I believe he is trying to replicate real world scenario and that is correct. Ideally you should be able to force failover if force quorum is able to bring WSFC online. – Shanky Jun 14 '17 at 17:01

Your Answer

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

Browse other questions tagged or ask your own question.