Best practice on performing maintenance on a 2 node SQL Server Cluster with no witness.

Theo 0 Reputation points
2024-05-17T11:16:51.44+00:00

Hello,

We have a two-node SQL Server Cluster with no witness.

Each month we patch the secondary server, then perform a failover and then patch the new secondary server.

We do nothing special to tell the cluster that a node is going offline for patching. Should we be telling it?

Yesterday, the secondary server was unexpectedly shutdown and we saw the cluster service get shutdown. We have event log entries such as:

  1. Cluster network interface 'Primary server - Ethernet 2' for cluster node 'Primary server' on network 'Cluster Network 1' failed.
  2. Cluster network 'Cluster Network 1' is down. None of the available nodes can communicate using this network.
  3. Cluster node 'Secondary server' was removed from the active failover cluster membership. The Cluster service on this node may have stopped.
  4. The Cluster service is shutting down because quorum was lost.
  5. Cluster node 'Primary server' failed to join the cluster because it could not communicate over the network with any other node in the cluster.
  6. Cluster failed to start.

Question: Without a witness server, how should we tell the cluster we are performing maintenance on the secondary server to prevent the cluster going down?

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Marcin Policht 15,620 Reputation points MVP
    2024-05-17T11:24:48.01+00:00

    Consider implement a witness instead. This can be as simple as a USB drive

    https://learn.microsoft.com/en-us/windows-server/failover-clustering/file-share-witness


    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    0 comments No comments

  2. Alex Bykovskyi 1,831 Reputation points
    2024-05-17T15:59:17.1366667+00:00

    Hey,

    As mentioned, it is better to add witness to your cluster. 2 node clusters are not redundant. File share witness is the easiest option. As mentioned, it could be done on a USB drive. https://techcommunity.microsoft.com/t5/failover-clustering/new-file-share-witness-feature-in-windows-server-2019/ba-p/372149

    You can also use StarWind VSAN to create HA device for a disk witness. Might help:

    https://www.starwindsoftware.com/resource-library/starwind-virtual-san-for-hyper-v-2-node-hyperconverged-scenario-with-windows-server-2016/

    Cheers,

    Alex Bykovskyi

    StarWind Software

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    0 comments No comments