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
Action performed ones there was no connectivity between Prod and DR:
- Logged-in to DR SQL Instance
- Stopped the Clustered Service
- Start the Cluster Service with Forced Quorum
- 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.
But AG Force fail over with Data loss command
ALTER AVAILABILITY GROUP [' + @availabilityGroup + '] FORCE_FAILOVER_ALLOW_DATA_LOSS;
gave below error:
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.
DR Database showed status Not Synchrinizing/Recovery Pending.
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.