question

DmitryKudlaenko-9468 avatar image
0 Votes"
DmitryKudlaenko-9468 asked DmitryKudlaenko-9468 commented

MS SQL Always-On failover scenario

Hi, everybody! I'm testing MS SQL Always-on failover scenarios. My setup is: 2 nodes in WFCS hold AO role (Windows server 2019, MS SQL 2017). Always-On configured with primary replica on the 1st node and secondary on the 2nd. Availability mode - "Synchronous commit", so replicas can switch between each other in case of one of the nodes' fail. I've tested failover mode by switching off network interface on one of the nodes, and cluster continues to work on the rest node and DB switch from primary to secondary replica. That's ok. Then I decided to check next scenario: what if network connection doesnt fail, but SQL service does? I tried to stop SQL service on the primary replica and the whole cluster failed. DB became unavailable. Is it the way it should go? Does it mean I have no any failover if SQL service crashes? Or did I do something wrong? Thanks for any help! PS Sorry for bad English btw

sql-server-generalwindows-server-clustering
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
2 Votes"
Criszhan-msft answered DmitryKudlaenko-9468 commented

Hi @DmitryKudlaenko-9468,

This issue may be related to the default configuration of your AG resources in WFCS, try to change the "Maximum Failures in the specified period" property to a higher value. Please check the following blog for more details.
https://docs.microsoft.com/en-us/archive/blogs/sqlserverfaq/inf-alwayson-the-secondary-database-doesnt-come-automatically-when-the-primary-instance-of-sql-server-goes-down

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DmitryKudlaenko-9468 If suggestion given by Criszhan does not helps please let us know. I believe this should help

0 Votes 0 ·

It really kinda works! Thank you for solution!

0 Votes 0 ·