LLI Wiki — My Digital Knowledge Base

AG: failover cluster timeout settings

WSFC network thresholds, availability group lease, session and health check timeouts, with the PowerShell and T-SQL commands to read and set them.

Failover cluster and availability group timeouts decide how quickly SQL Server fails over after a network or health problem. This article lists the cluster network thresholds, how to read and set them with PowerShell, and the availability group lease and health check timeouts.

Cluster network thresholds

Parameter Windows Server 2012 R2 Windows Server 2016 Maximum
SameSubnetDelay 1 second 1 second 2 seconds
SameSubnetThreshold 5 heartbeats 10 heartbeats 120 heartbeats
CrossSubnetDelay 1 second 1 second 4 seconds
CrossSubnetThreshold 5 heartbeats 20 heartbeats 120 heartbeats
CrossSiteDelay NA 1 second 4 seconds
CrossSiteThreshold NA 20 heartbeats 120 heartbeats

Reference: Tuning failover cluster network thresholds and Get-Cluster.

Read the current cluster parameters

Get-Cluster | Format-List -Property *

Set the maximum values (testing)

(get-cluster).SameSubnetDelay  = 2000 # milliseconds
(get-cluster).SameSubnetThreshold  = 120
(get-cluster).CrossSubnetDelay  = 4000 # milliseconds
(get-cluster).CrossSubnetThreshold = 120
(get-cluster).CrossSiteDelay = 4000 # milliseconds
(get-cluster).CrossSiteThreshold = 120

Set the default values for SQL Server 2016 (testing)

(get-cluster).SameSubnetDelay  = 1000  # milliseconds
(get-cluster).SameSubnetThreshold  = 10
(get-cluster).CrossSubnetDelay  = 1000
(get-cluster).CrossSubnetThreshold = 20
(get-cluster).CrossSiteDelay = 1000 # milliseconds
(get-cluster).CrossSiteThreshold = 20

Lease timeout

Reference: Availability group lease and health check timeout.

Health check values

HealthCheckTimeout is the time the resource DLL can go without receiving an update from sp_server_diagnostics. FailureConditionLevel sets which conditions trigger a failover. The update interval is HealthCheckTimeout / 3.

Get-ClusterResource | Get-ClusterParameter
Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq 'FailureConditionLevel' } |  ft ClusterObject,name,Value
Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq 'HealthCheckTimeout' } |  ft ClusterObject,name,Value
Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq 'LeaseTimeout' } |  ft ClusterObject,name,Value

Default values and availability group settings

Setting Default
Lease timeout 20000
Session timeout 10000
Health check timeout 30000
FAILURE_CONDITION_LEVEL 3

The health check timeout and failure condition level are set on the availability group:

ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 1);
ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 120000);

Set the health check and session timeouts with SMO

This script sets the health check timeout on every availability group of the instance and the session timeout on every replica.

Import-Module SqlServer

$serverCon = New-Object Microsoft.SqlServer.Management.Smo.Server sqlserver01
try {
    $serverCon.ConnectionContext.Connect()
    foreach ($ag in $serverCon.AvailabilityGroups) {
        Set-SqlAvailabilityGroup -HealthCheckTimeout 120000 -InputObject $ag
        foreach ($agr in $ag.AvailabilityReplicas) {
            Set-SqlAvailabilityReplica -SessionTimeout 50 -InputObject $agr
        }
    }
}
catch {
    Write-Error "Caught an exception: $($_.Exception.Message)"
    exit 1
}

Read the session timeout back:

$serverCon.AvailabilityGroups["AG1"].AvailabilityReplicas["sqlserver01"].Properties["SessionTimeout"].Value

Failure condition level on a failover cluster instance

For a failover cluster instance (FCI), the failure policy is set per cluster resource. The loop below sets FailureConditionLevel to 1 on each resource.

foreach ($resource in Get-ClusterResource) {
    Set-ClusterParameter FailureConditionLevel 1 -InputObject $resource
    Get-ClusterParameter -InputObject $resource
}

Notes

Mastodon LLIMastodon SocialFosstodon