SQL Server Always-On Availability Groups LeaseTimeout

Question

One of my AG environments encountered the error

The lease between availability group XXXXXXX and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Now I know why this issue occurred however I want to understand why the availability group crashed instead of failing over to another node i.e. primary role changed to resolving was the next error. It took around 1 minute for the availability group to come back online automatically on the same node. Surely it should failover to the next available node? This is a 5 node cluster all having votes within quorum.

Is this just the way SQL Availability groups work? i.e. if the AG loses connection to the windows failover cluster dll then it cannot failover and crashes to avoid a split brain scenario?

Where do I configure this default behavior of it trying to reconnect first before failing over to another node?

Answer 1

If you'd like to know why, the answer will be in the cluster log. If you need help looking over it, please upload the primary node's (at the time) cluster log.

Clustering makes the decisions when it comes to FCIs and AGs with SQL Server. The cluster is responsible for metadata updates and changes, health checking, resource placements, etc., and most people don't change the by default cluster behavior which is to restart the resource after a period of time.

Since in your case it connected back up one minute later, the best guess without the cluster log is that this is what occurred, especially since you've stated that the default clustering settings have not been changed.

More Info 1: https://learn.microsoft.com/en-us/previous-versions/orphan-topics/ws.11/dn281898(v=ws.11)?redirectedfrom=MSDN

More Info 2: https://support.microsoft.com/en-us/help/228923/cluster-resources-can-be-configured-to-restart-automatically

so does restarting the resource mean restarting the SQL instance?

No, it restarts the AG Resource which is not a SQL Server instance.

What I dont understand is, if its set to restart the SQL service before trying a failover (when it comes to a leasetimeout) - wouldn't a restart of the service/resource cause a failover anyway?

No, because it's not the instance. The availability group tied to that resource will stop synchronizing and will go into a resolving state but the instance itself will not have any issues.

Answer 2

As an add-on to Sean Gallardy's answer, the Auto fail-over basically depends on following areas:

1. FAILURE_CONDITION_LEVEL: (by default 3), in your case the incident looks like it fall under FAILURE_CONDITION_LEVEL = 2, by any-chance if you set it to 1 it won't consider higher level conditions i.e. 2, 3, 4 and 5 that results NO auto fail-over
2. Fail-over mode: If secondary replica's failover_mode not set to automatic (in particular AG Properties), the AG won't fail-over automatically in any FAILURE_CONDITION_LEVEL
3. Possible Owners at WSFC: If you select one node only in the list for AG resource and the clustered role that contains this resource cannot fail over.

With reference to point# 1 and 2, you can verify current configuration with following query:

select name, failure_condition_level 
from sys.availability_groups_cluster
go

select  replica_server_name, 
        endpoint_url, 
        availability_mode_desc,
        failover_mode_desc
from sys.availability_replicas
go