Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Step by Step Installation of Adding a New Node to a SQL Server 2014 Failover Cluster


By:   |   Read Comments (2)   |   Related Tips: More > Clustering

Attend these FREE MSSQLTips webcasts >> click to register


Problem

In the last tip, Step by Step Installation of SQL Server 2014 on a New Failover Cluster, we installed SQL Server 2014 on a new failover cluster. In this tip I will explain how to add SQL Server nodes to the cluster environment.

Solution

We have a two node cluster based on Windows Server 2012 R2 operating systems where we will be installing SQL Server 2014. The name of the nodes are SQL-NODE1 and SQL-NODE2. We have used MSSQLCLUSTER as the Network Name and virtual IP of 10.2XX.XX2.X5 (note: X represents a numeric value, I put X because I do not want to disclose my IP address due to security reasons). So let's install SQL Server 2014 on node SQL-NODE2.

SQL Server 2014 Cluster Node Addition

Step 1: On the new node where you want to install SQL Server, go to the SQL Server setup file location and right click on setup.exe and choose "Run as administrator". SQL Server Installation Center will appear on your screen as shown in the below screenshot. Select the "Installation" tab on the left side and click on the third option "Add node to a SQL Server failover cluster" from the right side.

SQL Server Installation Center

Step 2: Once you click on "Add node to a SQL Server failover cluster", the "Add a Failover Cluster Node" window will appear as shown below. We then need to go through several screens like this when we install SQL Server 2014 on the first node. We need to click the Next button through each of these screens: Microsoft Update, Product Updates, Install Setup Files, Add Node Rules, Product Key and License Terms.

global rules configuration

Step 3: Now SQL Server will ask you to configure the node to be added to the SQL Server failover cluster. The details about the SQL Server cluster instance which we have installed in our last tip will appear on the right. You can see the SQL Server cluster network name along with the SQL Server instance name. You can also see the features that have been installed on SQL-NODE1.

cluster node configuration

Step 4: Now click on the Next button to proceed with the installation. The next window will ask you to verify the cluster network configuration. You do not need to enter anything here, but you just need to validate the details like the SQL Server virtual IP which we used to install the SQL Server cluster. It will be same as we entered during the SQL Server cluster installation. Click on the Next button to go on to the next window.

cluster network configuration

Step 5: The next window will ask you to configure the SQL Server service accounts. Enter the password of the service accounts and click on the Next button.

service accounts

Step 6: The next window is the "Ready to Add Node" page to verify all the details as shown in the below screenshot

ready to add node

You can see the add node progress in the below screenshot.

progress bar

Once installation successfully completes, you will get the below screen with confirmation that all the features you have selected during the SQL Server cluster installation are successful.

progress bar


Validate the SQL Server Node Addition

Now that we have added this node to the SQL Server failover cluster, we want to validate the installation by querying a DMV.

You can see the below screenshot which was taken after we installed SQL Server 2014 on SQL-NODE1. You can see there is only one entry, because this was taken before the addition of SQL-NODE2 to the cluster.

Installations

Now if we run the same query again, both nodes with their ownership show in the below screenshot.

DMV output


SQL Server Failover Testing

Now we will test the failover configuration to see whether the cluster is working. Before going ahead, you can see the owner node is SQL-NODE1 in the above screenshot. After failover it should be SQL-NODE2.

Launch "Failover Cluster Manager", click on Roles from the left side pane. You can see the SQL Server instance on the right side pane with status, type, owner node and priority. Right click on the SQL Server role, click the "Move" option and choose "Best Possible Node" as shown below.

FCM Failover testing

There are two options to check the owner node. One is by querying the SQL Server DMV which we did in last section or to use the Failover Cluster Manager. Let's check both places.

You can see the owner node is shows SQL-NODE2 in the below screenshot.

DMV output

You can also see in the Failover Cluster Manager as shown below, that all SQL Server shared disks are now online for SQL-NODE2.

cluster resource group
Next Steps

Now we have successfully installed SQL Server 2014 on two node cluster. Follow this process if you need to install SQL Server 2014 on cluster environment.

Check out the first two tips in this series:

Read more SQL Server Clustering tips here



Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, July 22, 2016 - 8:54:45 AM - Tt Back To Top

Thank you for great tutorial.

Could you please advise how to troubleshoot the following situation:

 

Using SQL managements studio I connect to the active node with cluster IP.

Then I perform manual switchover to the second node.

My next attempt tpo connect to SQL cluster IP fails.

 

So I can use only one. 

Though validation was successful...

 

 

Thanks


Tuesday, March 22, 2016 - 7:19:17 PM - Gurpreet Singh Back To Top

 

Hi Manvendra,

Thanks for the Cluster Documentation. I have a question regarding building a High Availability cluster  ( MSSQL 2014) . How can I contact you?

 

Thanks

 

Gurpreet


Learn more about SQL Server tools