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.
- Do not lower any timeout below its default value.
- The lease interval (1/2 of LeaseTimeout) must be shorter than SameSubnetThreshold * SameSubnetDelay (120 * 2000 = 240000, so 120000).
- SameSubnetThreshold must be less than or equal to CrossSubnetThreshold.
- SameSubnetDelay must be less than or equal to CrossSubnetDelay.
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
- The timeout values in the tables are the ones given in the source; the health check timeout is 120000 in the summary and the SMO script, and 60000 in the example of the source's ALTER AVAILABILITY GROUP statement.
- The "maximum" and "testing" values are not recommendations for production. Lowering timeouts below their defaults is not advised.
- The SMO script was shortened: the logging wrapper of the source was removed.