azure – selectstarfromyogi

To modify the FailureConditionlevel value, use the Failover Cluster Manager and follow these steps:

In the roles tab, find the target AG role. Select the target AG role.
Right-click the AG resource at the bottom of the window and select Properties.
In the popup window, navigate to the properties tab and there will be a list of values specific to this AG. Click the FailueConditionLevel to change it.

The failure condition level of the AG changes the failure conditions for the health check. For any failure level, if the AG element is reported unhealthy by sp_server_diagnostics then the health check will fail. Each level inherits all the failure conditions from the levels below it.Expand table

Level	Condition under which the instance is considered dead
1: OnServerDown	Health check takes no action if any resources fail besides the AG. If AG data is not received within 5 intervals, or 5/3 * HealthCheckTimeout
2: OnServerUnresponsive	If no data is received from `sp_server_diagnostics` for the HealthCheckTimeout
3: OnCriticalServerError	(Default) If the system component reports an error
4: OnModerateServerError	If the resource component reports an error
5: OnAnyQualifiedFailureConitions	If the query processing component reports an error

Failure-Condition 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.

Important

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.Expand table

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.	1	OnServerDown
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.	2	OnServerUnresponsive
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.	3	OnCriticalServerError
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.	5	OnAnyQualifiedFailureConditions