Sql-server – SQL Server 2017 Always-ON

sql serversql server 2014sql-server-2012windows-server

I have configured Distributed AG from DC1 to DC2 (SQL 2017 Ent edition with CU22 version)

DC1- DB1, DB2 and DC2-DB3, DB4

  1. I have restarted DC1-DB1 SQL services, after restart DC1-DB2 should be primary. But still DC1-DB2 showing as secondary. Is this is the expected behaviour?

  2. I have restarted DC1-DB2 SQL services, nothing happen – still DC1-DB1 is primary. Is this is the expected behaviour?

DC1 AG configurations:

enter image description here

Best Answer

The exact similar question was asked on new Microsoft Q and A Site. I created a 2 node WSFC with AG configured for SQL Server databases. 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

This can be changed via Powershell, TSQL or from cluster GUI. One below shows value in cluster GUI

enter image description here

Related Question