Sql server Always On Primary server does not accept connection when the connections to witness and secondary server gone

Sql server Admin 26 Reputation points
2021-08-27T11:27:37.823+00:00

Let me tell you the structure of my alwayson setup; 

     Suppose that; Primary Server is A,  Secondary Server is B, and the File Share Witness server is C.

    Alwayson is configured as asynchronous mode and manual failover.

     Sometimes a problem occurs in network that simultaneously, the connection between A and B and the connection between A and C is lost
    f or 20-30 seconds, Primary server A , throws an error saying that; Whether A is primary or secondary server, i do not understand, that's why, i can not accept new connection.

   Orijinal Error:

  Message
 [298] SQLServer Error: 983, Unable to access availability database 'XXX' because the database replica is not in the PRIMARY or SECONDARY  role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. [SQLSTATE 42000]

 In fact Primary server A is up, there is no problem, if Alwayson was not set up, Primary server would continue to serve and accept connections from application servers.

    In Summary, If primary server A is up, i want primary server A to keep serving and accepting new connection even if it lost connections to secondary and witness server.
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,691 questions
Accepted answer
  1. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2021-08-30T07:27:00.54+00:00

    Hi @Sql server Admin ,

    The primary replica lost the connection with file share witness and secondary replica, the cluster does not achieve the majority, it fails to start the cluster services and the entire cluster is down.

    Nodes cannot communicate with each other. In this case, all cluster nodes assume that they need to bring the resources online, but it is not possible to do so. This situation is called a Split-Brain situation. Both the primary and secondary replicas went into the “RESOLVING” state. SQL server AG could not be accessed.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-08-27T21:59:03.713+00:00

    If the connection from A to the other two nodes are lost, the cluster has lost quorum, so there is not much that can work in this case.

    If your network is this shaky, maybe an AG is not the right solution for you. Maybe you should build your HA/DR on log shipping or replication instead.

    0 comments
  2. Sql server Admin 26 Reputation points
    2021-09-06T06:27:41.303+00:00

    Ok , i looked for a new solution, as you both say, i understand there is no a solution to go around the problem.

    0 comments