Configure a flexible automatic failover policy for an Always On availability group
Applies to: SQL Server - Windows only
This topic describes how to configure the flexible failover policy for an Always On availability group by using Transact-SQL or PowerShell in SQL Server. A flexible failover policy provides granular control over the conditions that cause automatic failover for an availability group. By changing the failure conditions that trigger an automatic failover and the frequency of health checks, you can increase or decrease the likelihood of an automatic failover to support your SLA for high availability.
The flexible failover policy of an availability group is defined by its failure-condition level and health-check timeout threshold. On detecting that an availability group has exceeded its failure condition level or its health-check timeout threshold, the availability group's resource DLL responds back to the Windows Server Failover Clustering (WSFC) cluster. The WSFC cluster then initiates an automatic failover to the secondary replica.
The flexible failover policy of an availability group cannot be configured by using SQL Server Management Studio.
Limitations on Automatic Failovers
For an automatic failover to occur, the current primary replica and one secondary replica must be configured for synchronous-commit availability mode with automatic failover and the secondary replica must be synchronized with the primary replica.
SQL Server 2019 (15.x) increased the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x). You can configure this group of five replicas to have automatic failover within the group. There is one primary replica, plus four synchronous secondary replicas.
If an availability group exceeds its WSFC failure threshold, the WSFC cluster will not attempt an automatic failover for the availability group. Furthermore, the WSFC resource group of the availability group remains in a failed state until either the cluster administrator manually brings the failed resource group online or the database administrator performs a manual failover of the availability group. The WSFC failure threshold is defined as the maximum number of failures supported for the availability group during a given time period. The default time period is six hours, and the default value for the maximum number of failures during this period is n-1, where n is the number of WSFC nodes. To change the failure-threshold values for a given availability group, use the WSFC Failover Manager Console.
- You must be connected to the server instance that hosts the primary replica.
|To configure the flexible failover policy for a new availability group||Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.|
|To modify the policy of an existing availability group||Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.|
Health-Check Timeout Threshold
WSFC resource DLL of the availability group performs a health check of the primary replica by calling the sp_server_diagnostics stored procedure on the instance of SQL Server that hosts the primary replica. sp_server_diagnostics returns results at an interval that equals 1/3 of the health-check timeout threshold for the availability group. The default health-check timeout threshold is 30 seconds, which causes sp_server_diagnostics to return at a 10-second interval. If sp_server_diagnostics is slow or is not returning information, the resource DLL will wait for the full interval of the health-check timeout threshold before determining that the primary replica is unresponsive. If the primary replica is unresponsive, an automatic failover is initiated, if currently supported.
sp_server_diagnostics does not perform health checks at the database level.
Whether the diagnostic data and health information returned by sp_server_diagnostics warrants an automatic failover depends on the failure-condition level of the availability group. The failure-condition level specifies what failure conditions trigger an automatic failover. There are five failure-condition levels, which range from the least restrictive (level one) to the most restrictive (level five). A given level encompasses the less restrictive levels. Thus, the strictest level, five, includes the four less restrictive conditions, and so forth.
Damaged databases and suspect databases are not detected by any failure-condition level. Therefore, a database that is damaged or suspect (whether due to a hardware failure, data corruption, or other issue) never triggers an automatic failover.
The following table describes the failure-condition that corresponds to each level.
|Level||Failure-condition||Transact-SQL Value||PowerShell Value|
|One||On server down. Specifies that an automatic failover is initiated when one the following occurs:
The SQL Server service is down.
The lease of the availability group for connecting to the WSFC cluster expires because no ACK is received from the server instance. For more information, see How It Works: SQL Server Always On Lease Timeout.
This is the least restrictive level.
|Two||On server unresponsive. Specifies that an automatic failover is initiated when one of the following occurs:
The instance of SQL Server does not connect to cluster, and the user-specified health check timeout threshold of the availability group is exceeded.
The availability replica is in failed state.
|Three||On critical server error. Specifies that an automatic failover is initiated on critical SQL Server internal errors, such as orphaned spinlocks, serious write-access violations, or too many memory dumps generated in a short period of time.
This is the default level.
|Four||On moderate server error. Specifies that an automatic failover is initiated on moderate SQL Server internal errors, such as a persistent out-of-memory condition in the SQL Server internal resource pool.||4||OnModerateServerError|
|Five||On any qualified failure conditions. Specifies that an automatic failover is initiated on any qualified failure conditions, including:
Detection of Scheduler deadlock.
Detection of an unsolvable deadlock.
This is the most restrictive level.
Lack of response by an instance of SQL Server to client requests is irrelevant to availability groups.
To configure the flexible failover policy
Connect to the server instance that hosts the primary replica.
To set the failover condition level, use the FAILURE_CONDITION_LEVEL = n option, where, n is an integer from 1 to 5.
For example, the following Transact-SQL statement changes the failure-condition level of an existing availability group,
AG1, to level one:
ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 1);
The relationship of these integer values to the failure condition levels is as follows:
Transact-SQL Value Level Automatic Is Failover Initiated When... 1 One On server down. The SQL Server service stops because of a failover or restart. 2 Two On server unresponsive. Any condition of lower value is satisfied, the SQL Server service is connected to the cluster and the health check timeout threshold is exceeded, or the current primary replica is in a failed state. 3 Three On critical server error. Any condition of lower value is satisfied or an internal critical server error occurs.
This is the default level.
4 Four On moderate server error. Any condition of lower value is satisfied or a moderate Server error occurs. 5 Five On any qualified failure conditions. Any condition of lower value is satisfied or a qualifying failure condition occurs.
For more information about the failover condition levels, see Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server).
To configure the health check timeout threshold, use the HEALTH_CHECK_TIMEOUT = n option, where, n is an integer from 15000 milliseconds (15 seconds) to 4294967295 milliseconds. The default value is 30000 milliseconds (30 seconds)
For example, the following Transact-SQL statement changes the health-check timeout threshold of an existing availability group,
AG1, to 60,000 milliseconds (one minute).
ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 60000);
To configure the flexible failover policy
Set default (cd) to the server instance that hosts the primary replica.
When adding an availability replica to an availability group, use the New-SqlAvailabilityGroup cmdlet. When modifying an existing availability replica, use the Set-SqlAvailabilityGroup cmdlet.
To set the failover condition level, use the FailureConditionLevellevel parameter, where, level is one of the following values:
Value Level Automatic Is Failover Initiated When... OnServerDown One On server down. The SQL Server service stops because of a failover or restart. OnServerUnresponsive Two OnCriticalServerError Three
This is the default level.
OnModerateServerError Four OnAnyQualifiedFailureConditions Five
For example, the following command changes the failure-condition level of an existing availability group,
AG1, to level one.
Set-SqlAvailabilityGroup ` -Path SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MyAg ` -FailureConditionLevel OnServerDown
To set the health check timeout threshold, use the HealthCheckTimeoutn parameter, where, n is an integer from 15000 milliseconds (15 seconds) to 4294967295 milliseconds. The default value is 30000 milliseconds (30 seconds).
For example, the following command changes the health-check timeout threshold of an existing availability group,
AG1, to 120,000 milliseconds (two minutes).
Set-SqlAvailabilityGroup ` -Path SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MyAG ` -HealthCheckTimeout 120000
To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.
To set up and use the SQL Server PowerShell provider
To configure automatic failover
Change the Availability Mode of an Availability Replica (SQL Server) (automatic failover requires synchronous-commit availability mode)
Overview of Always On Availability Groups (SQL Server)
Availability Modes (Always On Availability Groups)
Failover and Failover Modes (Always On Availability Groups)
Windows Server Failover Clustering (WSFC) with SQL Server
Failover Policy for Failover Cluster Instances