About SQL Server Cluster Failover Issue

Leo Ng 20 Reputation points
2024-01-11T08:35:18.3166667+00:00

HI, I have an question about SQL Server always on feature setup.

My server settings as below:

  1. An Server Farm consists of two SQL Servers (SQL Server 2019 Enterprise)
  2. Windows Cluster Configuration:
    1. Node: sql01.domain1.com, sql02.domain1.com
    2. Cluster: SqlCluster
    3. Quorum: Node Majority
  3. The Quorum for sql01 and sql02 is stored in \adServer01\sqlcluster
  4. For Quorum configuration
    1. Quorum Type: File Share Witness (\adServer01\sqlcluster)
    2. Voting Nodes: All nodes are configured to have quorum votes.
    3. Witness Resource: \adServer01\sqlcluster
    My questions: Q1. if primary node (Server sql01) and File Share Witness are not service down in the same time, will SQL server proceed failover to secondary node (server sql02)? Q2. if the failover cannot be proceed the above condition, any suggestion to enhance the high availability level of SQL Server? Thanks
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,432 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Azar 26,180 Reputation points MVP
    2024-01-11T08:49:44.2566667+00:00

    Hi Leo Ng Firs in a normal Always On Availability Group configuration, the failover decision is based on the health of the primary replica and the availability of a quorum. If the primary node (SQL01) remains healthy and the File Share Witness (\adServer01\sqlcluster) is accessible, a failover to the secondary node (SQL02) will not be triggered. As long as the primary node and the File Share Witness are available, the system will continue to operate with the primary node. secondly consider adding an extra File Share Witness for fault tolerance, configuring the cluster with Node Majority for distributed voting, exploring Azure's Cloud Witness for a cloud-based option, and ensuring regular monitoring and maintenance. Testing failovers in a controlled environment is crucial to identifying and addressing any potential issues proactively.

    https://learn.microsoft.com/en-us/search/?terms=Always%20On%20availability%20groups%20(SQL%20Server) Kindly accept the answer if this helped, thanks much.


  2. Erland Sommarskog 116.5K Reputation points MVP
    2024-01-11T22:32:06.6433333+00:00

    If both the File Share Witness and the primary node are down, and the secondary is all there is, the cluster has lost quorom, and will go down. There will be no failover. How to prevent this from happening? I guess more nodes for better redundancy, but I am not expert on high availability.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.