Always On Basic Availability Groups stuck as primary on both replicas

Question

I configured Always on basic availability groups on my SQL Server 2019 Standard instances. One as our production machine and the second as a DR machine.

I have primary set to synchronous commit and DR set to asynchronous commit. It seems to be working perfectly and failover works without any problems until I tested what would happen should our primary server completely fail and become unavailable.

To simulate this I turned off the SQL Server service on the primary and then did a forced manual failover to secondary. Secondary came online without any problems and all the recent changes were available (lucky for me - no data loss) but then when I started up the SQL Server service again on the original primary, the AG went into primary state while the secondary replica was also still in primary.

Now when I try doing a failover, it fails saying the database is already a primary on the other side. I tried this from both Prod and DR with no luck either side. Luckily for now I was just using a test database but we would like to implement Always on to replace the current log shipping we have in place but now with this happening I am scared of ending up in such a situation with a production DB.

Was I doing the failover incorrectly or is there some startup switches I am missing and how do I get the 2 replicas to act normal again (1 primary 1 secondary). I truly hope someone else has had this issue and managed to get it resolved as recovery from DR after failover with log shipping is a complete nightmare plus our data loss is always going to be high.

We have tried mirroring but ended up dropping it as the performance impact was very high plus we had a few instances where it did a DB failover without any problems on the primary database so mirroring is quite scary plus MS has deprecated it since SQL Server 2012.

Answer 1

...when I started up the SQL Server service again on the original primary, the AG went into primary state while the secondary replica was also still in primary.

This is called "split brain" and really shouldn't happen - SQL Server and WSFC have a lot of controls built in to protect against getting into this state. What should happen is that the original primary would come back up in the secondary role in the SUSPENDED state. Then you would just need to resume each database on that node to be back up and running normally.

It sounds like, after doing the forced failover, you also forced quorum in Windows Server Failover Clustering (WSFC). This would lead to the situation you described, because it overrides the protection controls built in to WSFC and says "I know what the state of the cluster should be."

...and how do I get the 2 replicas to act normal again (1 primary 1 secondary).

As far as I know, you'll need to choose the one that you want to be the new primary (the one with the most up-to-date / correct data), remove the other replica from the AG, re-add it, and re-initialize the databases on that new secondary replica (either by restoring from a backup of the primary, or using automatic seeding).

---

If quorum was not forced, another possibility is that you've configured a read-scale (clusterless) availability group. In that case, you need to manually handle all role changes for the nodes. See this related Q&A for details:

High availability primary does not automatically take secondary role when back online