Sql-server – SQL Server Cluster: There is no SQL Server failover cluster available to join

clusteringsql server

I have set up a SQL Server 2016 Standard cluster (Cluster-SQL) on node 1 (Cluster-Node-1) which is appearing in Windows Failover Cluster manager without issue.

When I attempt to add a second node (Cluster-Node-2) to this cluster and I reach the Cluster Node Configuration, there is no SQL Server instance name listed or available from the drop down.

My Net Admins are at a loss on this and I'm an accidental DBA from a programming background so any suggestions as to what to look for would be appreciated.

To date, we have disabled firewalls on both nodes of the cluster. The SPN's have been set up and I can access the SQL Cluster, Cluster-SQL using SSMS from both nodes.

Any other options to try that are obvious to more experienced DBA's?

Regards,
Brian

Best Answer

Sql Server account has to be the same on both machines, it should be Domain account ( not local user). It should have particular rights in domain, at least during installation, and it should be local administrator.
SQL server browser service should work.
You should not mess with reporting services nor "install all" option, because it may lead to slightly harder configurations scenario. It is better to focus on bare database engine services installation, and after add what you want.

Related Solutions

Sql-server – SQL Server cluster not available after failure of the “main” server

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

Sql-server – SQL Server: multiple failover cluster instances with disjoint storage

I've found the answer to this problem. It is possible, but you either need Windows 2008R2 SP1 or higher, or a hotfix. The full details are at:

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

To quote a line from this article: This arrangement of storage, where a cluster disk is shared between a subset of nodes within a WSFC, is referred to as asymmetric storage

Best Answer

Related Solutions

Sql-server – SQL Server cluster not available after failure of the “main” server

Sql-server – SQL Server: multiple failover cluster instances with disjoint storage

Related Question