Step-by-step Installation of SQL Server 2016 on a Windows Server 2016 Failover Cluster - Part 4

By:   |   Comments (10)   |   Related: 1 | 2 | 3 | 4 | > Clustering


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.
  • Part 2 created the WSFC using the Create Cluster Wizard.
  • Part 3 completed the installation of a single-node SQL Server 2016 FCI.

SQL Server 2016 Failover Cluster Instance Installation - Install Secondary (Failover) Cluster Node

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,

  1. 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.
  2. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2016 Setup wizard.

    3. installation center

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

    4. product key

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

    5. license terms

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

    6. global rules

  6. In the Microsoft Update dialog box, click Next.

    7. microsoft update

  7. 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.

    8. add node rules

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

    9. cluster node configuration

  9. 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.

    10. cluster network configuartion

  10. 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.

    11. 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.

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

    12. feature rules

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

    13. ready to add node

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

    14. complete

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.

Testing SQL Server 2016 FCI Manual Failover with Application Connectivity

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.

SELECT @@SERVERNAME AS InstanceName, 
SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS NodeName
SELECT * FROM sys.dm_os_cluster_nodes

server name

To test the failover process using the Failover Cluster Manager console,

  1. Expand Roles and select SQL Server (MSSQLSERVER).

    2. roles

  2. Right-click the SQL Server (MSSQLSERVER) role, select Move and click Select Node.

    3. failover cluster manager

  3. In the Move Clustered Role dialog box, select the node where you want the SQL Server FCI to move into. Click OK.

    4. move clustered role

  4. After the failover process completes, re-run the query above to verify that the SQL Server FCI is now running on the other node.

    5. instance name

Another way of performing a manual failover test is by using the Move-ClusterGroup PowerShell cmdlet as described in this previous tip.

Move-ClusterGroup "SQL Server (MSSQLSERVER)" -Node "WSFC2016-NODE2"

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, November 20, 2019 - 5:07:30 PM - bass_player Back To Top (83154)

Charles,

I've done several in-place upgrades in the past. It is possible. Whether or not you should do it is dependent on what you are trying to accomplish.

Thursday, November 7, 2019 - 9:28:20 PM - Charles J Locy Back To Top (83012)

Trying to find out if anyone has upgraded from SQL 2014 Cluster to SQL 2016 Cluster, we like to do an inplace upgrade but cannot find any info on it or if it is even possible or would be better to create another Cluster 2016 and migrate the databases...any help would be appreciated.

Tuesday, August 14, 2018 - 1:46:46 PM - Ayushi Gautam Back To Top (77178)

 Hi, 

My first question is that can we install SQL server 2016 on Windows server 2012, also if we need to upgrade the SQL version of a SQL instance running on SQL 2012 active active cluster on winWind 2012, can we install SQL 2016 on the same server and then migrate the SQL DB from SQL instance 2012 to SQL instance 2016 ? I am in a issue due to this, could you please help.

Tuesday, April 10, 2018 - 6:55:52 PM - bass_player Back To Top (75667)

 Unfortunately, S2D is only officially supported on SQL Server 2016 and higher. There isn't any official support statement on SQL Server 2014 and earlier

Tuesday, April 10, 2018 - 1:03:46 PM - Scott Back To Top (75665)

 

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).

Tuesday, April 10, 2018 - 12:35:20 PM - bass_player Back To Top (75664)

 

It works in multi-subnet configuration.

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.

 

Another thing that you would have to consider is networking. Your design would require proper planning and routing of the IP addresses for WSFC nodes in different availability zones.

Tuesday, April 10, 2018 - 10:26:07 AM - Scott Back To Top (75660)

 Does this also work for a multi-subnet configuration?  Example in AWS, node A being in AZ A and and node B being in AZ B.

Monday, April 9, 2018 - 10:26:33 AM - bass_player Back To Top (75646)

 

Hi JJJ,

It shows in the section heading: SQL Server 2016 Failover Cluster Instance Installation - Install Secondary (Failover) Cluster Node

Monday, April 9, 2018 - 8:22:25 AM - JJJ Back To Top (75645)

 This does not mention if you need to run this on primary or secondary node

 

Tuesday, December 19, 2017 - 9:54:46 AM - Azhar iqbal Back To Top (74245)

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

 














get free sql tips
agree to terms