I have two SQL Servers 2014 in AlwaysOn AG setup (OS – WSFC2012R2) with secondary acting as a synchronous replica. I'm facing an issue in the role switchover on daily basis.
Every day morning around 7 or 8AM, due to some reason Primary fails over to the secondary. Ideally, this happens due to network glitch. But i'm unable to find the root cause behind this failover.
This happens on daily basis. Is there any way to narrow down the issue which causes the failover?
Best Answer
The cluster log, SQL Server Error Log and the application log in Event Viewer should give you the reason.
It sounds like quorum is not setup properly. Without a 3rd resource to act as the witness, a network glitch will cause a failover.
Setup a witness, such as a file share on another server, or a witness disk. Some people use a file share on a file server or an AD server. I've also used the witness disk approach where the disk is a shared drive, like what you'd have on a Failover Cluster.