We recently had an issue where the network between our GEO-Cluster would go down and both Availability Group Instances thought they were supposed to take charge. When the network came back up, both of them still though they were in charge. You can imagine with an AG, you can’t have two instances that think they are in charge without problems. This brought up the question of how voting was configured between the two of them. This script helped a bunch:
SELECT member_name, member_state_desc, number_of_quorum_votes FROM sys.dm_hadr_cluster_members;
We found that the File Share Witness wasn’t working properly by checking the member state. In a simple AG, a good practice is to have each instance and then a File Share Witness,that keeps each side from accidentally taking over. You’re Welcome.
The song for this post: You’re Welcome From Moana