SQL Server Always ON

PraveenKumar 216 Reputation points
2020-09-22T14:11:29.307+00:00

Hi All,

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 server AG's configurations:
    26408-image.png
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
11,750 questions
0 comments No comments
{count} votes

Accepted answer
  1. Shashank Singh 6,246 Reputation points
    2020-09-22T14:32:21.147+00:00

    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?

    If you have gracefully restarted the SQL Server service from SSCM or services.msc SQL Server will not treat this as event to do failover. This is not "unexpected" for WSFC. The reason is 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  
    

    PS: With default value of 3, I believe if you go and kill SQL Server service from task manager then I believe a failover should happen, not sure though, test it.

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

    Restart of secondary replica does not affects primary.


1 additional answer

Sort by: Most helpful
  1. m 4,266 Reputation points
    2020-09-23T07:54:44.953+00:00

    Hi @PraveenKumar ,

    Please solve the issue by following steps to fail over to a secondary availability group in DAG as this : configure-distributed-availability-groups-failover

    Note: In a distributed availability group, we can do a manual failover. It does not support automatic failovers because the replicas are in different clusters.

    More information: monitor-and-failover-a-distributed-sql-server-always-on-availability-group

    BR,
    Mia


    If the answer is helpful, please click "Accept Answer" and upvote it.