LLI Wiki — My Digital Knowledge Base

Always On: cluster timeouts, lease/health check and node quarantine

Relax WSFC heartbeat settings and availability group timeouts, and handle a quarantined node.

Availability group failovers caused by short heartbeat or health-check timeouts, and nodes put in quarantine by Windows Server Failover Clustering (WSFC), are a common problem for SQL Server Always On. This article shows how to relax the cluster heartbeat settings and the AG timeouts, how to inspect the current values, and how to handle a quarantined node.

Cluster heartbeat settings

Values before and after the change:

Cluster property Previous value New value
SameSubnetDelay (ms) 1000 2000
SameSubnetThreshold 10 30
CrossSubnetDelay (ms) 1000 3000
CrossSubnetThreshold 20 60
CrossSiteDelay (ms) 1000 3000
CrossSiteThreshold 20 30
(Get-Cluster).SameSubnetDelay     = 2000   # milliseconds
(Get-Cluster).SameSubnetThreshold = 30
(Get-Cluster).CrossSubnetDelay    = 3000
(Get-Cluster).CrossSubnetThreshold = 60
(Get-Cluster).CrossSiteDelay      = 3000   # milliseconds
(Get-Cluster).CrossSiteThreshold  = 30

The same, using a cluster object:

$Cluster = Get-Cluster

$Cluster.CrossSiteThreshold   = 30
$Cluster.CrossSiteDelay       = 3000

$Cluster.CrossSubnetThreshold = 60
$Cluster.CrossSubnetDelay     = 3000

$Cluster.SameSubnetThreshold  = 30
$Cluster.SameSubnetDelay      = 2000

Availability group timeouts

The lease interval (half of LeaseTimeout) must be shorter than SameSubnetThreshold * SameSubnetDelay. With a threshold of 30 and a delay of 2000 ms: 30 * 2000 = 60000, divided by 2 gives a LeaseTimeout of 30000 ms.

ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 1);
ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 40000);
ALTER AVAILABILITY GROUP AG1 SET (LEASE_TIMEOUT = 30000);

Check the current values on the cluster resources

Get-ClusterResource | Get-ClusterParameter | Where-Object { $_.Name -eq 'HealthCheckTimeout' } |
    Format-Table ClusterObject, Name, Value

Get-ClusterResource | Get-ClusterParameter | Where-Object { $_.Name -eq 'FailureConditionLevel' } |
    Format-Table ClusterObject, Name, Value

Get-ClusterResource | Get-ClusterParameter | Where-Object { $_.Name -eq 'LeaseTimeout' } |
    Format-Table ClusterObject, Name, Value

Example of what was found before the change (one line per AG resource): HealthCheckTimeout 30000, FailureConditionLevel 3, LeaseTimeout 20000.

Node quarantine

A node that leaves the cluster too often is quarantined and cannot host roles for a while. References:

Useful Windows event IDs

Category Event ID Description Meaning
Quarantine 1641 Node quarantine activated The node is officially quarantined.
1647 Node cannot host roles during quarantine No role, VM or AG can be hosted on this node.
1649 Quarantine state updated State transition or attempt to leave quarantine.
Communication / heartbeat 1135 Node removed from cluster Heartbeat lost, node ejected (main cause).
1177 Cluster communication issue Cluster communication channels failed.
10054 Connection reset Dropped packets, firewall, MTU, NIC or antivirus.
Storage / CSV 5120 CSV paused/disconnected Storage access lost or CSV disconnected.
5142 CSV operational error Unstable MPIO/storage driver, lost path.
Services / RHS 7031 Unexpected service termination RHS/Cluster Service crash, a frequent cause of quarantine.
Configuration / quorum 1069 Cluster resource failure A cluster resource failed.
1558 Quorum witness issue Witness unreachable or unstable quorum.
121 Cluster network resource failure Cluster IP/network name problem.

Inspect and clear the quarantine

# State of all nodes
Get-ClusterNode

# Quarantine settings
Get-Cluster | Format-List Quarantine*
Get-ClusterNode -Name SQLNODE2 | Format-List Name, State

# Force a node out of quarantine
Start-ClusterNode -Name SQLNODE2 -ClearQuarantine

If needed, evict and re-add the node, or restart the cluster service.

Tune the tolerance (number of failures) and the quarantine duration:

(Get-Cluster).QuarantineThreshold = 2
(Get-Cluster).QuarantineDuration  = 3600   # seconds (1 hour)

Script: apply and validate the settings (PowerShell)

Applies the heartbeat settings and the AG resource parameters, then compares expected and actual values.

#requires -RunAsAdministrator

$ErrorActionPreference = 'Stop'

Import-Module FailoverClusters

# ==========================================
# Configuration
# ==========================================
$ClusterName = 'sqlcluster01'
$AgName      = 'AG1'

$Expected = @{
    SameSubnetDelay       = 2000
    SameSubnetThreshold   = 30

    CrossSubnetDelay      = 3000
    CrossSubnetThreshold  = 60

    CrossSiteDelay        = 3000
    CrossSiteThreshold    = 30

    LeaseTimeout          = 30000
    HealthCheckTimeout    = 40000
}

try {
    # ==========================================
    # Retrieve cluster and AG resource
    # ==========================================
    $Cluster = Get-Cluster -Name $ClusterName

    $AgResource = Get-ClusterResource -Cluster $ClusterName |
        Where-Object {
            $_.ResourceType -eq 'SQL Server Availability Group' -and
            (
                [string]$_.OwnerGroup -eq $AgName -or
                $_.Name -like "*$AgName*"
            )
        } |
        Select-Object -First 1

    if (-not $AgResource) {
        throw "Cluster resource for Availability Group '$AgName' was not found."
    }

    Write-Host "`nAvailability Group resource found:" -ForegroundColor Cyan

    $AgResource |
        Select-Object Name, State, OwnerGroup, OwnerNode, ResourceType |
        Format-List

    # ==========================================
    # Apply cluster heartbeat settings
    # ==========================================
    Write-Host 'Applying cluster heartbeat settings...' -ForegroundColor Cyan

    $Cluster.SameSubnetDelay       = $Expected.SameSubnetDelay
    $Cluster.SameSubnetThreshold   = $Expected.SameSubnetThreshold

    $Cluster.CrossSubnetDelay      = $Expected.CrossSubnetDelay
    $Cluster.CrossSubnetThreshold  = $Expected.CrossSubnetThreshold

    $Cluster.CrossSiteDelay        = $Expected.CrossSiteDelay
    $Cluster.CrossSiteThreshold    = $Expected.CrossSiteThreshold

    # ==========================================
    # Apply AG cluster resource settings
    # ==========================================
    Write-Host 'Applying Availability Group cluster parameters...' -ForegroundColor Cyan

    $AgResource |
        Set-ClusterParameter `
            -Name 'LeaseTimeout' `
            -Value $Expected.LeaseTimeout

    $AgResource |
        Set-ClusterParameter `
            -Name 'HealthCheckTimeout' `
            -Value $Expected.HealthCheckTimeout

    # ==========================================
    # Refresh values
    # ==========================================
    $Cluster = Get-Cluster -Name $ClusterName

    $AgResource = Get-ClusterResource -Cluster $ClusterName |
        Where-Object {
            $_.ResourceType -eq 'SQL Server Availability Group' -and
            (
                [string]$_.OwnerGroup -eq $AgName -or
                $_.Name -like "*$AgName*"
            )
        } |
        Select-Object -First 1

    $AgParameters = @{}

    $AgResource |
        Get-ClusterParameter |
        ForEach-Object {
            $AgParameters[$_.Name] = $_.Value
        }

    # ==========================================
    # Build validation table
    # ==========================================
    $Checks = @(
        [pscustomobject]@{
            Parameter = 'SameSubnetDelay'
            Expected  = $Expected.SameSubnetDelay
            Actual    = $Cluster.SameSubnetDelay
        }
        [pscustomobject]@{
            Parameter = 'SameSubnetThreshold'
            Expected  = $Expected.SameSubnetThreshold
            Actual    = $Cluster.SameSubnetThreshold
        }
        [pscustomobject]@{
            Parameter = 'CrossSubnetDelay'
            Expected  = $Expected.CrossSubnetDelay
            Actual    = $Cluster.CrossSubnetDelay
        }
        [pscustomobject]@{
            Parameter = 'CrossSubnetThreshold'
            Expected  = $Expected.CrossSubnetThreshold
            Actual    = $Cluster.CrossSubnetThreshold
        }
        [pscustomobject]@{
            Parameter = 'CrossSiteDelay'
            Expected  = $Expected.CrossSiteDelay
            Actual    = $Cluster.CrossSiteDelay
        }
        [pscustomobject]@{
            Parameter = 'CrossSiteThreshold'
            Expected  = $Expected.CrossSiteThreshold
            Actual    = $Cluster.CrossSiteThreshold
        }
        [pscustomobject]@{
            Parameter = 'LeaseTimeout'
            Expected  = $Expected.LeaseTimeout
            Actual    = $AgParameters['LeaseTimeout']
        }
        [pscustomobject]@{
            Parameter = 'HealthCheckTimeout'
            Expected  = $Expected.HealthCheckTimeout
            Actual    = $AgParameters['HealthCheckTimeout']
        }
    )

    $Results = $Checks |
        Select-Object Parameter, Expected, Actual,
            @{
                Name = 'Status'
                Expression = {
                    if ([string]$_.Expected -eq [string]$_.Actual) {
                        'OK'
                    }
                    else {
                        'NOT OK'
                    }
                }
            }

    # ==========================================
    # Display validation
    # ==========================================
    Write-Host "`nValidation results:" -ForegroundColor Cyan

    $Results |
        Format-Table -AutoSize

    $FailedChecks = $Results |
        Where-Object Status -ne 'OK'

    if ($FailedChecks) {
        Write-Host "`nSome settings are NOT OK." -ForegroundColor Red

        $FailedChecks |
            Format-Table -AutoSize

        $ExitCode = 1
    }
    else {
        Write-Host "`nAll cluster settings are OK." -ForegroundColor Green
        $ExitCode = 0
    }

    # ==========================================
    # AG resource status
    # ==========================================
    Write-Host "`nAvailability Group resource status:" -ForegroundColor Cyan

    $AgResource |
        Select-Object Name, State, OwnerGroup, OwnerNode |
        Format-Table -AutoSize

    if ($AgResource.State -ne 'Online') {
        Write-Host "`nWarning: the Availability Group resource is not Online." -ForegroundColor Red
        $ExitCode = 1
    }

    exit $ExitCode
}
catch {
    Write-Host "`nERROR: $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}

Script: apply and verify the AG settings (T-SQL)

USE [master];
GO

DECLARE @AgName sysname = N'AG1';

IF NOT EXISTS (
    SELECT 1
    FROM sys.availability_groups
    WHERE name = @AgName
)
BEGIN
    THROW 50000, 'Availability Group AG1 not found on this instance.', 1;
END;
GO

PRINT 'Applying Availability Group settings...';
GO

ALTER AVAILABILITY GROUP [AG1]
SET (FAILURE_CONDITION_LEVEL = 1);
GO

ALTER AVAILABILITY GROUP [AG1]
SET (HEALTH_CHECK_TIMEOUT = 40000);
GO

PRINT 'Availability Group settings applied.';
GO

/* Verification */
SELECT
    @@SERVERNAME AS ConnectedInstance,
    ag.name AS AvailabilityGroup,
    ag.failure_condition_level AS FailureConditionLevel,
    CASE ag.failure_condition_level
        WHEN 1 THEN 'ON_SERVER_DOWN'
        WHEN 2 THEN 'ON_SERVER_UNRESPONSIVE'
        WHEN 3 THEN 'ON_CRITICAL_SERVER_ERROR'
        WHEN 4 THEN 'ON_MODERATE_SERVER_ERROR'
        WHEN 5 THEN 'ON_ANY_QUALIFIED_FAILURE_CONDITIONS'
        ELSE 'UNKNOWN'
    END AS FailureConditionDescription,
    ag.health_check_timeout AS HealthCheckTimeoutMs,
    CASE
        WHEN ag.failure_condition_level = 1
         AND ag.health_check_timeout = 40000
        THEN 'OK'
        ELSE 'NOT OK'
    END AS ValidationStatus
FROM sys.availability_groups AS ag
WHERE ag.name = N'AG1';
GO

Notes

Mastodon LLIMastodon SocialFosstodon