4

I aksed this question on stackoverflow, but was asked to repost here so here it is:

I have a database that I'm setting up in a clustered environment. It'll be two servers with a shared storage. ServerA is the server where I setup the database, and ServerB is a node in the SQLServer cluster.

If I forcibly turn off ServerB everything keeps on working as expected, but if I turn off ServerA the instance is not available anymore.

Windows cluster is still alive (I can remote desktop onto the cluster via shared name), but the SQL is not visible, and cluster manager reports the resource as offline.

This kinda defeats the purpose of the cluster and I need to solve it, but it's not really in my area of expertise, and I have no idea where to start.

Thanks in advance.

EDIT (Additional info asked in comments): Windows was installed I added clustering feature for DTC and SQL Server. As for access, IP addresses of the machines are *.101 for main; *.102 for second server; *.99 for access point SQL server instances and *.111 for cluster manager. I can access 99 from both machines but can't access database at 101 or 102 from either.

EDIT #2 - I tried increasing the failover threshold (to 50 in 24 hours, for out testing purposes), but the server still doesn't wake up after cutting off the network for server1. The cluster comes back in about 20 sec or so, but SQL Server stays down. In the logs I have serveral errors and one Critical level message that is probably causing the fail. Message is:

The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Improve this question
5
  • What version of SQL Server? What version of Windows?
    – Hannah Vernon ♦
    Commented Nov 5, 2013 at 14:17
  • Windows Server 2008 R2 Enterprise and SQL Server 2008 R2 Standard Edition.
    – Fedor Hajdu
    Commented Nov 5, 2013 at 14:21
  • When installing SQL Server on a cluster, the setup program should run through a cluster validation process. I assume the validator didn't find any problems?
    – Hannah Vernon ♦
    Commented Nov 5, 2013 at 14:23
  • I got a single warning, wrote it down but didn't think much about it. It said something like Network Binding Order "Domain network is not first bound" Is that related?
    – Fedor Hajdu
    Commented Nov 5, 2013 at 14:25
  • You should be able to re-run Setup on both machines (without actually doing the install) to see what it thinks about your configuration.
    – Hannah Vernon ♦
    Commented Nov 5, 2013 at 14:42

1 Answer 1

Reset to default
5

Without looking at the cluster logs or any other form of error reporting, all I can do is guess here.

But my initial thoughts are that you may have hit the failover threshold. By default, this is going to be set to a maximum of n - 1 failures (where n is the number of nodes) over a period of 6 hours. Yes, that's a long time, and especially in a 2-node cluster that isn't very many failures (only equates to one failure). This threshold is set to prevent the Ping-Pong effect of cluster groups.

In production, this is probably a good thing. But in testing/development/non-prod it is pretty common to run into this initially perplexing problem, as you may be trying to consecutively failover. It is worth noting that these parameters are 100% configurable. All you need to do is go into the properties of the cluster group, and in the "failover" tab you will have the option to change these two parameters (Maximum failures in the specified period and Period (hours). Here is what this looks like in the Failover Cluster Manager:

enter image description here

Note: in my screenshot, the threshold is set to 2 because I have a 3-node cluster.

Likewise, this can be seen with PowerShell (accessing the FailoverClusters module).

# you may need to set your cluster group name to whatever it is named
# in your environment
#
Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)" | 
    Select-Object Name, FailoverThreshold, FailoverPeriod
Improve this answer
8
  • I can't check it today, but it seems like it is the problem. We had many forced failures in a short period of time (we're testing the environment) so this is most likely it. Will check it tomorrow and accept the answer if it solves it. Thanks!
    – Fedor Hajdu
    Commented Nov 5, 2013 at 15:55
  • Please see my edit
    – Fedor Hajdu
    Commented Nov 7, 2013 at 7:48
  • 2
    You basically lost quorum on the cluster. You need to figure out why you lost quorum. Is this a two node cluster? Do you have a node and disk majority? If so, that means there are 3 votes, and for a quorum you'll need to have 2 votes. You could sustain the failure of one of those votes. If anymore, then you lost quorum.
    – Thomas Stringer
    Commented Nov 7, 2013 at 11:13
  • 1
    We've set cluster to No Majority for this reason but the issue still persisted.
    – Fedor Hajdu
    Commented Nov 7, 2013 at 11:27
  • 2
    In that quorum model the disk is a single point of failure. I would look at why communication with the quorum disk was lost.
    – Thomas Stringer
    Commented Nov 7, 2013 at 11:33

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.