SQL AAG Cluster - Diagnosing Partner Quorum Failure

Question

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.

https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/quorum-how-a-witness-affects-database-availability-database-mirroring?view=sql-server-ver15

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 MultiSubnetFailover=True.

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

Get-ClusterLog: https://gist.github.com/4oo4/572515faf7634ad7a24c225c39e05848

Answer 1

From the data given, the problem seems to be that of either performance or some other item which affected the node(s) around that time. It's inconclusive from the data given.

2021-10-23 03:12:26.26 Server      Always On: The local replica of availability group 'MyDb4' is going offline because either the lease expired or lease renewal failed.

This is what SQL Server says to be the case and it's quite true. The lease was killed by the cluster because it didn't get the output of sp_server_diagnostics in within the healthcheck period.

00000054.00001ae8::2021/10/23-08:12:21.086 INFO  [RES] SQL Server Availability Group: [hadrag] Availability Group with resource ID '010c15c7-6b6a-4c70-a40a-1c918e69010c' did not receive healthinformation before HealthCheckTimeout
00000054.00001ae8::2021/10/23-08:12:21.086 ERR   [RES] SQL Server Availability Group <mydb4>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
00000054.00001ae8::2021/10/23-08:12:21.086 ERR   [RES] SQL Server Availability Group <mydb4>: [hadrag] Resource Alive result 0.
00000054.00001548::2021/10/23-08:12:21.087 ERR   [RES] SQL Server Availability Group <mydb4>: [hadrag] Lease Thread terminated

There seems to have been an issue with performance on the SQL Server or with validation of credentials (such as high cpu on a domain controller, etc.) as the cluster attempted to do the vanilla response of restarting the resource after the default delayed time. The resource restarted but the connection to SQL Server had failed.

00000054.00002b80::2021/10/23-08:12:41.374 ERR   [RES] SQL Server Availability Group <mydb3>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: Timeout error [258].  (258)
00000054.00002b80::2021/10/23-08:12:41.374 ERR   [RES] SQL Server Availability Group <mydb3>: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0)
00000054.00002b80::2021/10/23-08:12:41.374 ERR   [RES] SQL Server Availability Group <mydb3>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in prelogin response (258)
00000054.00002b80::2021/10/23-08:12:41.374 INFO  [RES] SQL Server Availability Group <mydb3>: [hadrag] Could not connect to SQL Server (rc -1)
00000054.00002b80::2021/10/23-08:12:41.374 INFO  [RES] SQL Server Availability Group <mydb3>: [hadrag] SQLDisconnect returns following information
00000054.00002b80::2021/10/23-08:12:41.397 ERR   [RES] SQL Server Availability Group <mydb3>: [hadrag] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
00000054.00002b80::2021/10/23-08:12:41.397 ERR   [RES] SQL Server Availability Group <mydb3>: [hadrag] Failed to connect to SQL Server
00000054.00002b80::2021/10/23-08:12:41.397 INFO  [RES] SQL Server Availability Group <mydb3>: [hadrag] Offline call failed in Terminate function

Again, the default response from the cluster was completed which is to restart the resource again. This time the connection succeeded and the AG was able to come online for a short amount of time (3 minutes) before having another issue:

00000054.000005bc::2021/10/23-08:12:57.036 INFO  [RES] SQL Server Availability Group <mydb1>: [hadrag] Connect to SQL Server ...
00000054.000005bc::2021/10/23-08:12:59.178 INFO  [RES] SQL Server Availability Group <mydb1>: [hadrag] The connection was established successfully
2021-10-23 03:13:02.70 spid60      Always On: The local replica of availability group 'MyDb1' is preparing to transition to the primary role. This is an informational message only. No user action is required.
2021-10-23 03:16:52.05 Server      SQL Server hosting availability group 'MyDb1' did not receive a process event signal from the Windows Server Failover Cluster within the lease timeout period.

Seems like the same performance issue still exists:

00000054.000029a8::2021/10/23-08:17:20.029 ERR   [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
00000054.000029a8::2021/10/23-08:17:20.029 ERR   [RES] SQL Server Availability Group <mydb1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
00000054.000029a8::2021/10/23-08:17:20.029 ERR   [RES] SQL Server Availability Group <mydb1>: [hadrag] Resource Alive result 0.