I'm relatively new to high availability groups in SQL Server, and am hoping for a bit of advice on best practices for configuration. I didn't set up this configuration, I inherited it and am trying to get an understanding as I've been moved towards a more DB Admin-focused role of late. There's a specific issue we encountered recently which I'll try to explain.
We have a SQL Server cluster on 2019 with two nodes and a file share witness. Load balancing enabled, low aggressiveness. I noticed the file share witness is configured to fail over if the restart period fails (set to 10 minutes).
Anyways, the problem. Last night our WSUS server installed updates which took the file share witness offline and triggered a failover to our secondary node. However, since the problem wasn't with any of the database availability groups, they all remained on the primary node, rendering them inaccessible on the cluster.
EDIT: This is an availability group setup alone; the file share witness was on the primary node at the time. The file share witness is owned by the primary node. Logs seem to indicate that the fsw failed a periodic health check when it was rebooting/installing updates, timed out of the automatic restart attempts, was marked as failed, and triggered a failover from primary to secondary. None of the availability groups on the primary node failed over to the secondary.
I assume that either the file share witness shouldn't be triggering a failover, or there's something I should be doing to make sure everything fails over when triggered. I just wanted to reach out to see if anyone had any advice, or anything I should be aware of to better prevent problems like this.
Thanks for your time. If there's anything I can add to clarify things, please let me know.