0

I have a new 3 node Windows 2022, SQL Enterprise 2022 cluster with file share witness. I am testing this before it goes fullly into production. I was hoping that it would run from only one node, but if I shut two down, no availability groups will start. I even tried changing the quorum and removed the two down nodes, but still I cannot get any AG to start on the one remaining node.

Is this by design? Would I need to evict the other nodes to get the AG roles to start? I can't easily test that as I guess I would need to reseed the other nodes and there are some big databases on there already.

If this is by design, it seems like a flaw. Here I am with one perfectly functioning SQL Server with alll the databases I need but I can't get them on line.

Thanks!

Improve this question
2
  • What is the configured value for REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT (SELECT name, required_synchronized_secondaries_to_commit FROM sys.availability_groups;)?
    – Dan Guzman
    Commented Apr 17, 2023 at 14:20
  • Hi Dan. All 4 AGs show 0 .
    – Michael Edwards
    Commented Apr 19, 2023 at 8:04

2 Answers 2

Reset to default
0

Is this by design?

Hard to say, there isn't any data right now. What does the cluster log say? Does it say it hit a limit of failures before backing off for a period of time? Is it saying it doesn't have quorum? You'd have to look at the logs to see why it isn't starting up.

You'd need to post the WSFC logs which should be 1 log for each node. You can generate them using PowerShell Get-ClusterLog -Health -UseLocalTime -Destination C:\SomePathForLogs

[...] but if I shut two down, no availability groups will start.

I'd expect the AG to still come online, assuming it's properly configured (WSFC + AG).

Would I need to evict the other nodes to get the AG roles to start?

Nope.

Improve this answer
8
  • Thanks a lot Sean. I ran the PowerShell command and all the log files generated just have soon headers for Operational, diagnostic and HealthAgent with no other entries.
    – Michael Edwards
    Commented Apr 19, 2023 at 8:18
  • This seems to imply this is by design and a three node cluster will never come on line with only one node up:
    – Michael Edwards
    Commented Apr 19, 2023 at 8:19
  • techcommunity.microsoft.com/t5/failover-clustering/…
    – Michael Edwards
    Commented Apr 19, 2023 at 8:20
  • "Having ‘quorum’, or a majority of voters, is based on voting algorithm where more than half of the voters must be online and able to communicate with each other. Because a given cluster has a specific set of nodes and a specific quorum configuration, the cluster will know how many "votes" constitutes a majority of votes, or quorum. If the number of voters drop below the majority, the cluster service will stop on the nodes in that group. "
    – Michael Edwards
    Commented Apr 19, 2023 at 8:21
  • In my case with three nodes (I have removed the file share witness, so only three nodes to "vote"), a single node is <50% so cluster service stopped. To me this appears to be by design
    – Michael Edwards
    Commented Apr 19, 2023 at 8:21
0

I believe that my issue was having a witness added to a three node cluster. This therefore gave four votes. I have since removed the file share witness and now when I repeat the test the cluster will happily run even if only one node is powered on.

Thank you to those that answered my post.

Improve this answer
3
  • That's not how it works, FYI. There is dynamic witness which will kick in. The testing methodology is actually flawed as losing 50% or greater will cause quorum loss. By default 3 nodes + witness the witness will not have a vote. Your environment needs to be examined and understood for the issues that you're having, removing the witness is more of a symptom of a larger issue that the issue itself.
    – Sean Gallardy
    Commented Apr 24, 2023 at 16:31
  • Thanks a lot Sean. Have realised eventually that the issue was the AG's in SQL had one node configured as manual failover. So failover to that node could never occur by using FCM but works just fine with SSMS.
    – Michael Edwards
    Commented Apr 25, 2023 at 18:39
  • You should never fail over an AG using failover cluster manager (FCM) or PowerShell commands to move a group as this will have a chance to cause SQL and the cluster to get metadata out of sync.
    – Sean Gallardy
    Commented Apr 25, 2023 at 19:51

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.