Problem

In a previous tip on
Validating a Windows Cluster Prior to Installing SQL Server 2014, I have seen
how to install SQL Server 2014 on a Windows Server 2012 R2 failover cluster (WSFC).
With Windows Server 2016 already publicly available, I would like to upgrade and
migrate my SQL Server 2008 failover clusters to SQL Server 2016 running on Windows
Server 2016. How do I go about building a Windows Server 2016 failover cluster for
SQL Server 2016 and eventually upgrade and migrate my databases?

Solution

To continue this series on Step-by-step Installation of SQL Server 2016
on a Windows Server 2016 Failover Cluster, we will look at adding a node
to an existing SQL Server 2016 failover clustered instance (FCI).

Part 1 completed the installation of the Failover Clustering feature on
both of the servers that will be used as part of the WSFC and ran the Failover
Cluster Validation Wizard.

Now that you have a working SQL Server 2016 FCI, you make it highly available
by adding nodes. To add a node to an existing SQL Server 2016 FCI,

Run setup.exe from the SQL Server 2016 installation media
to launch SQL Server Installation Center. Click on the
Installation link on the left-hand side.

Click the Add node to a SQL Server failover cluster link.
This will run the SQL Server 2016 Setup wizard.

In the Product Key dialog box, enter the product key that
came with your installation media and click Next.

In the License Terms dialog box, click the I accept
the license terms check box and click Next.

In the Global Rules dialog box, validate that the checks
return successful results and click Next.

In the Microsoft Update dialog box, click Next.

In the Add Node Rules dialog box, validate that the checks
return successful results. If the checks returned a few warnings, make sure
you fix them before proceeding with the installation. Click Next.

In the Cluster Node Configuration dialog box, validate
that the information for the existing SQL Server 2016 FCI is correct. Click
Next.

In the Cluster Network Configuration dialog box, validate
that the IP address information is the same as the one you provided in
the previous tip.

In the Service Accounts dialog box, verify that the information
is the same as what was used to configure the first node. Provide the appropriate
credentials for the corresponding SQL Server service accounts.

NOTE: Pay close attention to the order of the SQL Server
services especially when you use different service accounts. In the
previous tip, you see the SQL Server Agent service come before the SQL Server
Database Engine service. Here, it's the reverse - the SQL Server Database Engine
service comes before the SQL Server Agent service. Be sure not to mix those
two up.

Select the checkbox Grant Perform Volume Maintenance Task privilege
to SQL Server Database Engine Service to enable Instant File Initialization
for SQL Server as highlighted in this
tip. Because this is a local permission assigned to an account, you need
to explicitly do this on all of the nodes in the SQL Server FCI.

Click Next.

In the Feature Rules dialog box, verify that all checks
are successful. Click Next.

In the Ready to Add Node dialog box, verify that all configuration
settings are correct. Click Install to proceed with the installation.

In the Complete dialog box, click Close.
This concludes adding a node to an existing SQL Server 2016 FCI.

To add more nodes to the SQL Server 2016 FCI, simply repeat steps #1 to #13.

At the completion of a successful installation and configuration of the node,
you need to validate whether the SQL Server 2016 FCI will failover - either automaticaly
or manually - to all of the available nodes.

A simple way to test whether or not the SQL Server 2016 FCI works is to perform
a manual failover. This process involves moving the SQL Server cluster resource
group/role from one node to another.

For this test, a simple query using SQL Server Management Studio as the client
application can be used. It is recommended to perform this test with application
connectivity to observe how the application behaves during the failover process.
Refer to the query below and connect to the SQL Server 2016 FCI.

Testing SQL Server 2016 FCI Automatic Failover

The ultimate test of validating whether or not a SQL Server FCI works is to invoke
automatic failover by simulating real failure. While this is easier said than done
when you only access your servers remotely, it is still recommended to perform these
tests. The following can be performed to simulate failure of a WSFC node and initiate
automatic failover of the SQL Server FCI. Note that these steps need to be performed
using remote control management tools like HPE's iLO or an IP-based KVM (keyboard,
video and mouse) - not Microsoft Remote Desktop - if you are accessing the servers
remotely. You can also ask the data center engineers to unplug cables to simulate
real failure.

Reboot nodes

Disable network adapters

Kill Microsoft Failover Cluster Service - clussvc.exe

Kill SQL Server service - sqlservr.exe

Congratulations! You now have a working two-node SQL Server
2016 FCI running on Windows Server 2016.

Next Steps

Review the previous tips on Install SQL Server 2008 on a Windows Server
2008 Cluster
Part 1,
Part 2,
Part 3 and
Part 4 to see the difference in the setup experience between Windows Server
2008 and Windows Server 2016.

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
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Thanks bass_player, I was looking to utilize this for our Server 2016 DC/SQL 2014+ EC2's out of the box instead of relying on SIOS/Starwind additional costs. It looks like we could use Storage Spaces Direct in the same AZ in AWS, and Storage Replica's in different AZ (multisubnet).

However, the cloud is very much different than on-premises. For one, most of them don't support shared storage. You either implement a SQL Server failover clustered instance (FCI) using an SMB share or an emulated shared storage like that of SIOS DataKeeper or StarWind Virtual SAN. If you run Windows Server 2016, you can leverage storage spaces direct natively as your emulated shared storage. Refer to the following tips for more information.

Thanks Edwin for such a wonderful and step by step explanation. Great article.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.