0

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.

1
  • 1
    That is .. odd. Assuming best practice setup, if the witness had gone down then NEITHER node should have initiated failover. The primary would have continued to be so and the secondary should have only promoted if manually told to do so. The witness is meant to help prevent split brain. So given the facts as you have presented them, I would assume something fishy is going on. Sep 29, 2023 at 13:00

1 Answer 1

2

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.

I don't understand this, nor did you specify if this was an AG, FCI, or a combo. It's also unclear the topology. Is the FSW on a non-primary node in the same cluster which was rebooted? The FSW is on some other server which became unavailable and the database failed over but didn't? I'm just not following. This would need to be more clear to help in any meaningful way.

I assume that either the file share witness shouldn't be triggering a failover [...]

That depends on the state of the cluster. Generally, in a cluster that has no issues and everything is proper, losing access to the FSW will not cause anything, not even a blip.

Please update the question with more information including topology and sequence of events.


[...] the file share witness was on the primary node at the time [...]

If the FSW is literally on the same nodes as the cluster, that's an incorrect configuration and should be changed.

Logs seem to indicate that the fsw failed a periodic health check when it was rebooting/installing updates [...]

If the FSW is on the primary node, then the primary node also rebooted. This means, according to the only surviving node of the cluster (the secondary replica node) means it believes it's partitioned. When it called for a vote, it only hears back from itself, since the other node which has a vote and the FSW which is hosted on the node that is down won't be able to weigh in.

This means that partition lost quorum and should shut itself down (not physically poweroff, just clustering services) as to not split-brain.

[...] and triggered a failover from primary to secondary.

This wouldn't happen, since the cluster partition did not have quorum.

Everything that happened seems to be correct given the improper (I know you said you inherited this) configuration of the cluster.

2
  • 1
    Added some more information, I hope that helps a little. I'm sorry for the confusion.
    – fuandon
    Sep 29, 2023 at 14:31
  • @fuandon - Updated. Sep 29, 2023 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.