I've completed this setup:

OS: Windows server 2016

Database: SQL Server 2017

2 node availability group configured with automatic failover on both nodes. Fileshare witness configured, both nodes enabled readable secondary replicas.

Database showing synchronized on both nodes. Availability group state: Healthy, configured normal quorum and Fileshare witness and node 1, node 2 all three has 1 vote and state is online.

The question is I cannot access a database when I stopped SQL service on one of the nodes. Failover occurs to DB2 and status changed to primary when I stopped SQL service on DB1. When I try to select from BikeStores in DB2 this error shows:

database lacks a quorum of nodes for high availability.

Is my configuration wrong? or am I trying to achieve an impossible scenario in availability group?

Is there anyone who experienced the same situation?

I think I've configured quorum correct. Fileshare witness has 1 vote and the other 2 nodes have 2 votes. If 1 node fails quorum should've 2 online votes ready. So, why is the database lacks a quorum of the node for high availability when stop service on one of the nodes. I've tried stopping SQL service on primary & secondary result is same, when I stop service on secondary I cannot access the database from the primary node either. When two nodes online it is working perfectly fine. Can access r/w from primary and ReadOnly from secondary. Manual failover working fine.

The purpose of deploying availability group is to make high availability but it is not working when one node down.

New contributor
Duke is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    You'll need to do some troubleshooting--only 10,000 things that can go wrong and there's no way we can guess what's happening. Look at SQL Server error logs on the servers and cluster messages in the event logs. – Tony Hinkle Mar 25 at 11:28
  • I suggest you visit your Windows Server Failover Cluster (WSFC) configuration including Quorum set up. – SqlWorldWide 2 days ago

Your Answer

Duke is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.