MS SQL Always-On failover scenario

Question

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

Answer 1

Hi @Dmitry Kudlaenko ,

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://learn.microsoft.com/en-us/archive/blogs/sqlserverfaq/inf-alwayson-the-secondary-database-doesnt-come-automatically-when-the-primary-instance-of-sql-server-goes-down