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.