Sql-server – Fail to start sql cluster role

clusteringsql serversql-server-2008-r2

I have installed SQL Server using Failover Clustering on 2 nodes, running Windows Server 2012 R2 Enterprise Edition and SQL Server 2008R2 Standard SP1. Shared storage is provided by an iSCSI NAS device.

If I move the services to node 1, then shutdown node 2, SQL Server continues to run, as expected, on node 1. However, if I then shutdown node 1, and restart it, SQL Server fails to startup with the following error:

The resource has failed to come online due to the failure of one or more provider resources.

Why can't I start SQL Server?

Best Answer

In order for SQL Server services to start as part of a Windows Failover Clustering Service, quorum needs to be attained by at least one node in the cluster. If a two-node cluster has one node down, that node cannot contribute to the quorum, so some other method is necessary for the node that is up to attain quorum. Quorum is essentially a set of votes to see which node gets to run SQL Server. Without quorum, no node can run the service, which is the symptoms you are seeing.

The MSDN page on Failover Clustering quorums states:

Why configure the quorum?

To increase the high availability of the cluster, and the roles that are hosted on that cluster, it is important to set the cluster quorum configuration appropriately.

The cluster quorum configuration has a direct effect on the high availability of the cluster, for the following reasons:

  • It helps ensure that the failover cluster can start properly or continue running when the active cluster membership changes. Membership changes can occur because of planned or unplanned node shutdown, or when there are disruptions in connectivity between the nodes or with cluster storage.

  • When a subset of nodes cannot communicate with another subset of nodes (a split cluster), the cluster quorum configuration helps ensure that only one of the subsets continues running as a cluster. The subsets that do not have enough quorum votes will stop running as a cluster. The subset that has the majority of quorum votes can continue to host clustered roles. This helps avoid partitioning the cluster, so that the same application is not hosted in more than one partition.

  • Configuring a witness vote helps the cluster sustain one extra node down in certain configurations. For more information about configuring a quorum witness, see Witness configuration later in this topic.

Be aware that the full function of a cluster depends on quorum in addition to the following factors:

  • Network connectivity between cluster nodes

  • The capacity of each node to host the clustered roles that get placed on that node

  • The priority settings that are configured for the clustered roles

For example, a cluster that has five nodes can have quorum after two nodes fail. However, each remaining node would serve clients only if it had enough capacity to support the clustered roles that failed over to it and if the role settings prioritized the most important workloads.

You need to configure the failover cluster with one of the following options:

  • Node majority (no witness) - Only nodes have votes. No quorum witness is configured. The cluster quorum is the majority of voting nodes in the active cluster membership.

  • Node majority with witness (disk or file share) - Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes in the active cluster membership plus a witness vote. A quorum witness can be a designated disk witness or a designated file share witness.

  • No majority (disk witness only) - No nodes have votes. Only a disk witness has a vote. The cluster quorum is determined by the state of the disk witness. The cluster has quorum if one node is available and communicating with a specific disk in the cluster storage. Generally, this mode is not recommended, and it should not be selected because it creates a single point of failure for the cluster.

Related Question