Configure FailureConditionLevel property settings

Applies to: SQL Server

Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart. Changes to this property are applied immediately without requiring a restart of the Windows Server Failover Cluster (WSFC) service or the FCI resource.

Before You Begin

FailureConditionLevel Property Settings

The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions. This means that with each level, there is an increased probability of a failover or restart. For more information, see the "Determining Failures" section of the Failover Policy for Failover Cluster Instances topic.

Security

Permissions

Requires ALTER SETTINGS and VIEW SERVER STATE permissions.

Using PowerShell

To configure FailureConditionLevel settings
  1. Start an elevated Windows PowerShell via Run as Administrator.

  2. Import the FailoverClusters module to enable cluster cmdlets.

  3. Use the Get-ClusterResource cmdlet to find the SQL Server resource, then use Set-ClusterParameter cmdlet to set the FailureConditionLevel property for a Failover Cluster Instance.

Tip

Every time you open a new PowerShell window, you need to import the FailoverClusters module.

Example (PowerShell)

The following example changes the FailureConditionLevel setting on the SQL Server resource "SQL Server (INST1)" to fail over or restart on critical server errors.

Import-Module FailoverClusters  
  
$fci = "SQL Server (INST1)"  
Get-ClusterResource $fci | Set-ClusterParameter FailureConditionLevel 3  
  

Using the Failover Cluster Manager Snap-in

To configure FailureConditionLevel property settings:

  1. Open the Failover Cluster Manager snap-in.

  2. Expand the Services and Applications and select the FCI.

  3. Right-click the SQL Server resource under Other Resources, and then select Properties from the menu. The SQL Server resource Properties dialog box opens.

  4. Select the Properties tab, enter the desired value for the FailureConditionLevel property, and then click OK to apply the change.

Using Transact-SQL

To configure FailureConditionLevel property settings:

Using the ALTER SERVER CONFIGURATIONTransact-SQL statement, you can specify the FailureConditionLevel property value.

Example (Transact-SQL)

The following example sets the FailureConditionLevel property to 0, indicating that failover or restart will not be triggered automatically on any failure conditions.

ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY FailureConditionLevel = 0;  

See Also

sp_server_diagnostics (Transact-SQL)
Failover Policy for Failover Cluster Instances