2

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.

SQL Server Installation - Cluster Node Configuration

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

  • does your node2 already had set up a failover clustered sql server installed? does node 1 and node 2 can see other ? – Edgar Allan Bayron Dec 22 '17 at 9:46
  • Hi Edgar,SQL isn't installed at all on node2. The SQL failover cluster was installed on node1.Both nodes can see each other and they are listed in the nodes of the Windows Failover cluster. – bmoyno Dec 22 '17 at 9:50
  • The SQL Server Browser service is running on node1 also btw. The TCP/IP protocol is enabled too. – bmoyno Dec 22 '17 at 9:52
  • you have to install SQL Server with Failover cluster on node2 :) – Edgar Allan Bayron Dec 22 '17 at 9:58
  • Thanks Edgar, the options from the landing page are: "New SQL Server failover cluster installation" and "Add note to a SQL Server failover cluster". For node1 I chose "New SQL Server failover cluster installation" and this completed as "Cluster-SQL". For node2, I picked "Add note to a SQL Server failover cluster" and this is not picking up the SQL Cluster. Are you saying I should have chosen the same option as node1? – bmoyno Dec 22 '17 at 10:45
1
  1. 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.
  2. SQL server browser service should work.
  3. 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.
  • Hi Kakaz, I can confirm 1 (apart from the the "particular rights in domain", can you expand on this please?), 2 and 3 are in line with your suggestions. Can you expand on the what rights the SQL Server account should have on the domain please? – bmoyno Jan 2 '18 at 9:29
  • SQL server cluster nodes has to join Active Directory, and during installation account should have rights to create particular entries in AD and associated DNS service. Even creation of special AD container is suggested! Pleasea look at SQL cluster check list on Microsoft site. Of course it is very poorly written, as usual with MS products, but it should work if you work hard enough to understand it. It is do complicated I do not feel comfortable tto explain it here. If you don't have enough time for it, you have to follow default for Microsoft dark path: install everything as domain administr – kakaz Jan 2 '18 at 9:34
1

Thank you for giving your time to this. It is appreciated I can tell you as being stuck somewhere with what feels like zero options is not very pleasant.

The answer, for us anyway, to this issue was to enable admin shares to both nodes of the cluster. Our Group Policy disables admin shares on all machines but admin shares are required by SQL Server when adding cluster nodes as it needs to perform a "discovery" of the other nodes.

This step happens after the option to "Add note to a SQL Server failover cluster" has been selected.

A simple check is to try to:

  • Access \Cluster-Node-2\C$ through Windows Explorer from Cluster-Node-1
  • Access \Cluster-Node-1\C$ through Windows Explorer from Cluster-Node-2

If you can do both then admin shares are enabled, if you can't then talk to your Net Admins to enable admin shares so you can install and add nodes to a SQL Server cluster.

There is also a very useful log file for each attempted install in a path similar to:

  • C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\

The folders in here are date and time stamped and the file you are looking to review is:

  • Detail.txt

Ultimately a simple solution but as an accidental DBA with no access or permissions to Group Policy or Active Directory it was difficult to track down.

Luckily I have a very good and pro-active team of Net Admins to work with.

NB: This solved more than just this issue for us as the time the SQL Server installation wizard was taking to come up reduced from 7.5 hours approximately to 2 seconds after clicking an option from the landing page.

0

Node 2 needs to have SQL Server installed "with failover cluster" to be seen from node 1 configuration wizard.

  • Hi Edgar, the SQL Server failover cluster installation was installed on node1 and is up and running and available to the windows cluster also. I now want to add node2 as the second node of my SQL cluster. The node itself is available already to the windows cluster. – bmoyno Dec 22 '17 at 10:53
  • Does your Node 2 have SQL Server? – Edgar Allan Bayron Dec 22 '17 at 10:59
  • No, but I expected SQL to be installed when selecting "Add node to a SQL Server failover cluster". – bmoyno Dec 22 '17 at 11:09
  • No. Install SQL Server on NODE 1 AND NODE 2. – Edgar Allan Bayron Dec 22 '17 at 12:54
  • Hi Edgar, thanks for the advice but this would appear to go against Microsoft's own guidance on setting up a SQL cluster. Everything I have read has said to select "Add node to a SQL Server failover cluster". – bmoyno Jan 2 '18 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.