SQL Server Always ON

Question

SQL Server Always ON

PraveenKumar 316

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

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?
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:

Accepted answer

1 additional answer

Your answer

Answer 1

Shashank Singh 6,251

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.

PraveenKumar 316 Reputation points

2020-09-23T05:25:48.583+00:00

HI @Shashank Singh ,

I have tried to change FailureConditionLevel 1 value from PS but got below error. Please suggest.

Tried to change the value from TSQL, got below error.
Shashank Singh 6,251 Reputation points

2020-09-23T05:34:55.7+00:00

@PraveenKumar Can you follow GUI method and let me know what happens. Also check let me know the default value.

Open the Failover Cluster Manager snap-in.

Expand the Services and Applications and select the FCI.

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

Select the Properties tab, enter the desired value for the FaliureConditionLevel property, and then click OK to apply the change.
RatheeshKNair 6 Reputation points

2020-09-23T05:48:59+00:00

Make sure you run it on the Primary Node. Also for AG run the below to change the condition

ALTER AVAILABILITY GROUP [AGNAME] SET (FAILURE_CONDITION_LEVEL = 3);
PraveenKumar 316 Reputation points

2020-09-23T07:11:38.957+00:00
Hi @Shashank Singh and @RatheeshKNair ,

I have changed value from 3 to 1.

I have restarted SQL services manually on DC1-DB1. After restart DC1-DB2 is primary and DC1-DB1 is secondary (this is correct)

I have restarted SQL services manually on DC1-DB2 (primary node), but nothing changed. Still DC1-DB2 is primary and DC1-DB1 is secondary (After SQL service restart on DC1-DB2, it should be secondary and DC1-DB1 should be priary).

Not sure whats the problem here.....

Value changed from 3 (default) to 1:

After restarting SQL services on DC1-DB2, still DB2 is showing as primary (it should be secondary):
Shashank Singh 6,251 Reputation points

2020-09-23T07:46:13.737+00:00

@PraveenKumar Can you double check value of failureconditionlevel on DC1 DB2.

Answer 2

m 4,276

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.

PraveenKumar 316 Reputation points

2020-09-23T08:42:00.98+00:00

Hi @Yuximiao-MSFT ,

As per my understanding, within DC, it will allow automatic failover (dc1-db1 to dc1-db2 and vice-versa and dc2-db3 to dc2-db4 and vice-versa). Is this my understanding is correct?

Failover from dc1 to dc2 and vice-versa it's manual failover.
PraveenKumar 316 Reputation points

2020-09-23T14:28:10.617+00:00

Please confirm. Is it posisble or not to perform automatic failover within DC's.

My requirement: When I restart DC1-DB1 SQL services (not rebbot), DC1-DB2 should be primary, when I restart DC1-DB2 SQL services (not reboot), DC1-DB1 should be primary (same for Dc2)

Thanks in advance.
Shashank Singh 6,251 Reputation points

2020-09-23T15:27:07.867+00:00

That is a wrong answer posted by @Yuximiao-MSFT. They dont read question and post some random answer. Within same WSGC AG you have automatic failover. You have manual failover in other WSFC AG. Now I guess your issue was solved right ?
PraveenKumar 316 Reputation points

2020-09-24T03:33:03.027+00:00

Hi @Shashank Singh ,

Still issue is not resolved. I have made required changes on dc1-db1 (using UI on primary node of wsfc) as per your suggestion. please see the above screenshots.

When I restart SQL services on dc1-db1 (not reboot), still dc1-db1 is showing as primary node (it shoud be secondary and dc1-db2 shoud be primary). I hvae tried to restart dc1-db2 sql services (not reboot), nothing changed (dc1-db1 primary, dc1-db2 secondary).

Please suggest is there any other configuration changes required.
Shashank Singh 6,251 Reputation points

2020-09-24T05:00:50.863+00:00

You yourself told it is working

I have changed value from 3 to 1.

I have restarted SQL services manually on DC1-DB1. After restart DC1-DB2 is primary and DC1-DB1 is secondary (this is correct)
PraveenKumar 316 Reputation points

2020-09-24T07:02:15.67+00:00

Hi @Shashank Singh ,

I am able to achieve below results with VM reoot not with SQL Service restart. Changing Failure-Condition Level=1 didn't worked for me.

Reboot dc1-db1 and make dc1-db2 as primary (auto)
Reboot dc1-db2 and make dc1-db1 as primary (auto)

Thanks for your help on this.
Shashank Singh 6,251 Reputation points

2020-09-24T07:08:25.783+00:00

I need to test then but I believe that SQL Server service restart via SSCM or services.msc will be considered as a normal event since this is gracious restart done by user and will not be considered as something to cause failover. I will get back to you
Shashank Singh 6,251 Reputation points

2020-09-24T15:47:06.943+00:00

@PraveenKumar I tested and I am able to get AG failover after restarting SQL Server service with FailureConditionSet=1. set in AG resource properties. This I was able to get both nodes of AG. I am not sure what and how you are trying

Share via

SQL Server Always ON

1 additional answer

Your answer