3

I am trying to test the SQL Server AlwaysOn availability group behavior of my application database. I have created the availability group and able to simulate a failo ver from cluster manager, or from SQL Server studio correctly. The failover will also happen if the server active node was restarted.

As a last test, I restarted the SQL Server's windows service on active node. I was expecting the AG will fail over to the secondary node as soon as the service stops, but it didn't. the primary node lived through the service restart.

when the service was down momentarily, there was no switching of nodes. Does that mean that failover will happen only if the service was down for some threshold? if so, what is that threshold?

Dhawalk
  • 1,257
  • 13
  • 28

1 Answers1

0

Replying to a old thread so that anyone who reads it can benefit.

Does that mean that failover will happen only if the service was down for some threshold? if so, what is that threshold?

No this is not the case. To simply put WSFC did not initiated a failover because the parameter Failure-Condition Level is default 3. And as per Microsoft official document Configure a flexible automatic failover policy for an Always On availability group for a SQL Server restart to trigger a failover you need Failure-Condition Level=1

You can change the value either by using Powershell or Cluster GUI or TSQL. See Configure FailureConditionLevel property settings

Ex:

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
Shanky
  • 607
  • 4
  • 23
  • `each level includes all the conditions from the previous levels in addition to its own conditions.` - https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-instances?view=sql-server-ver16#determine. This means 3 includes the properties of 1 and 2. Please review this answer. – variable Jun 19 '23 at 03:36