Database stuck in a read only state when testing a DR scenario with a multi-subnet SQL Server Always On AG, how can I get the db in a healthy state?



  • I am testing a disaster recovery scenario with azure VMs. I have sql servers deployed across 2 regions joined together via Always On availability groups sitting on top of a multi-subnet WSFC. When I break the connection between the 2 regions (vnet) and try to bring the remote secondary replicas online as primary I encounter this error:

    Msg 35220, Level 16, State 1, Line 3 Could not process the operation. Always On Availability Groups replica manager is waiting for the host computer to start a Windows Server Failover Clustering (WSFC) cluster and join it. Either the local computer is not a cluster node, or the local cluster node is not online. If the computer is a cluster node, wait for it to join the cluster. If the computer is not a cluster node, add the computer to a WSFC cluster. Then, retry the operation.

    I made multiple attempts (via powershell/WSFC manager) to try to get the cluster group to come online. I tried a force start and a force start ignoring quorum. I also tried adjusting the nodeweight but to no avail. How do I get past this error and force the AG and/or WSFC to acknowledge that it is the primary and therefore allow the database to move to a read/write state?



  • You'll need to force quorum on the partition in the cluster you want, then proceed to bring the AG online by issues a failover with data loss. There is a https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-disaster-recovery-through-forced-quorum-sql-server?msclkid=51030cdcbb5a11ecbf65d90bbd6fae05&view=sql-server-ver15 on Docs which describes this in more detail with links.

    Once this is done, there is no going back. One side or the other will need to be reseeded with the databases and it's going to knock the non-forced quorum side of the cluster offline and grab the new cluster database from the forced side. I understand this is a test, but it's one that is destructive.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2