MS SQL Always-On failover scenario

Dmitry Kudlaenko 21 Reputation points
2020-11-23T08:57:02.963+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,756 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
959 questions
0 comments
Accepted answer
  1. Cris Zhan-MSFT 6,606 Reputation points
    2020-11-23T10:03:17.973+00:00

    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

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest