AOAG not failover when server is unresponsive

Question

We usually have some issues with one of our servers. We notice it was related to RAM and we are already dealing with it.

In the meantime, when the server becomes unresponsive, it will just lock all queries ( or just not respond ), and by that, it will not failover ( because server is frozen ).

What would be a good approach for a health check and, in case the server get's crazy, it will failover?

I was thinking about pinging via cmd and if it doesn't get a response after X times, it would failover but the server pings even when it's frozen.Also I tried a count for LCK sessions but, thats dangerous since during a normal day, it can reach for 1 or 2 seconds high ammounts of lcks.

Answer 1

[...] it will not failover ( because server is frozen ).

You should still be able to fail the AG over. There are two parts to this, and if the server isn't responding then the cluster health checks (both SQL and Windows) should both fail and the node should either bugcheck (with something such as a watchdog), or eventually the node resets either by human intervention or self mitigating.

Failover is executed on the node you want to become the primary replica which means it's currently a secondary replica. If your primary is freezing you can still fail over to a secondary, it just might need to be a forceful failover depending on how stuck the cluster on the unresponsive node is being.

What would be a good approach for a health check and, in case the server get's crazy, it will failover?

Generically something simple such as select 1 with a timeout of 2 seconds or something. You can also infer various items from performance counters outside of SQL Server, or even the XE data that is stored in the Log folder (though can be changed).

There's no perfect way as there are different issues that arise.