Recently we had a failure of our SQL Server (2016) Always On Availability Group, where our fileshare witness node was briefly inaccessible to our two nodes due to a CPU spike.
We have two SQL server nodes in opposite datacenters, and a fileshare witness server that lives in the same datacenter as our primary replica. I know that not having the witness in a 3rd datacenter isn't ideal, but we keep this server in the same datacenter as our primary replica, and can easily bring it up in our second datacenter if we need to failover.
According to Microsoft's docs, our primary/secondary roles should've stayed the same and reached a partner quorum, but instead we reached a split brain scenario, where neither replica thought it was primary, and flapped back and forth between both sites. We couldn't make the database available until we shut down the secondary replica.
After investigating, I noticed that we had duplicate DNS records for both availability groups, one record pointing to each datacenter. Our availability groups were stuck in "resolving" states, until I removed the duplicate DNS records.
Per AMTwo, this is normal since we have
To prevent a recurrence, I'm needing to understand why a partner quorum wasn't reached. Interestingly, I didn't see any event IDs related to witness failures. Instead, I only saw a vague error about cluster resources failing, before it flapped between the two sites and then reached its failure threshold.
I'm not super familiar with the internals of how AAG works, so this is what I still don't understand:
- Is it an AAG bug that a soft failure of our witness server would end up creating duplicate DNS records and creating a split brain?
- Was the partner quorum not able to be reached because it was a witness soft failure, versus the witness being unreachable?
- Could this be caused by not having the witness in its own datacenter,
or a misconfiguration of our Windows DNS server?
SQL Server Error Logs: https://gist.github.com/4oo4/b27446d209d0ff4b079c1af6b7dd8a04
Windows Cluster Event Logs: https://gist.github.com/4oo4/a56e63c4730f03935ca495e674e1c2a7