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

By: Edwin Sarmiento   |   Comments (6)   |   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 installing SQL Server 2016 on top of the existing Windows Server 2016 Failover Cluster (WSFC). In Part 2, you have learned how to create a Windows Server 2016 Failover Cluster using both the Create Cluster Wizard and the Failover Clustering PowerShell cmdlets. You've also renamed the shared storage and cluster network resources. This tip will walk you through the installation of a SQL Server 2016 failover clustered instance (FCI) on the WSFC.

Installing a SQL Server 2016 Failover Clustered Instance (FCI)

Starting with SQL Server 2012, there are two ways to install SQL Server on top of a WSFC - the traditional SQL Server failover clustered instance (FCI) and SQL Server Availability Groups (AG). Installing a SQL Server FCI requires shared storage or an emulated form of shared storage. Using an emulated form of shared storage will be covered in a future tip on using storage spaces direct (S2D) with a SQL Server 2016 FCI. SQL Server AG does not require any form of shared storage. In fact, you can use a standalone instance or a SQL Server FCI to configure SQL Server AG.

A common question that is frequently asked when dealing with a SQL Server FCI is, "can I convert a standalone instance to a SQL Server FCI and vice versa?" The answer is "no, you can't." If you want to convert a standalone instance to a SQL Server FCI or vice versa, you will have to treat it like a database migration process. So, before you proceed with this step, be very sure that a SQL Server FCI is what you really need. This series of tips is for installing a SQL Server FCI - not a SQL Server AG - on top of a WSFC.

In this section, you will install a default instance of SQL Server 2016 FCI. You will run the installation process on the first node of your WSFC.

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 New SQL Server failover cluster installation link. This will run the SQL Server 2016 Setup wizard.
   





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





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





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





6. In the Microsoft Update dialog box, you have the option to include SQL Server product updates like service packs and cumulative updates in the installation process. By default, it searches for product updates thru the Microsoft Updates service online, assuming that the server has access to the Internet. In cases where your servers do not have access to the internet, you can manually download the updates and store them on a network shared folder. You can, then, point the installation media to search the network shared folder instead. For this option, you will need to run setup.exe from the command-line, passing the /UpdateSource parameter. A more detailed approach to using this feature is outlined in this Microsoft documentation. Click Next.
   






NOTE: Another way to include SQL Server service packs and cumulative updates in the installation process is to create a slipstreamed installation media. Refer to this tip for the steps on how to do this.

7. In the Install Failover Cluster 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.

Be aware that you will get a warning result for Microsoft Cluster Service (MSCS) cluster verification warnings as a side effect of the storage spaces direct (S2D) checks described in this tip.




8. In the Feature Selection dialog box, select the following components Database Engine Services and Client Tools Connectivity. Click Next.

Note that SQL Server Management Studio is no longer included in the SQL Server 2016 installation media and have to be downloaded separately.




9. In the Feature Rules dialog box, verify that all the rules have passed. If the rules returned a few warnings, make sure you fix them before proceeding with the installation. Click Next.
   





10. In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that the client applications will use to connect to this server. A couple of things need highlighting in this section. By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for this specific instance of SQL Server - very helpful when you want to run multiple instances in a WSFC. It applies to both the default instance and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, you should specify a value in the Instance ID box. Click Next.
    





11. In the Cluster Resource Group dialog box, check the resources available on your WSFC. This tells you that a new Resource Group will be created on your WSFC for the SQL Server FCI. To specify the SQL Server cluster resource group name, you can either use the drop-down box to specify an existing group to use or type the name of a new group to create it. Accept all the defaults and click Next.
    





12. In the Cluster Disk Selection dialog box, select the available disk groups that are on the WSFC for the SQL Server FCI to use. Click Next. This is where you'll see the value of renaming the shared disk resources as done in Part 2 of this tip series.
    





13. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server FCI will use. Be sure to deselect the checkbox under the DHCP column as you will be using static IP addresses. Click Next. Again, this is where you'll see the value of renaming the cluster network resources as done in Part 2 of this tip series.
    





14. In the Server Configuration dialog box, provide the credentials for the SQL Server service accounts in the Service Accounts tab. Make sure that both the SQL Server Agent and SQL Server Database Engine services have a Startup Type of Manual. The WSFC will take care of stopping and starting these services.

Select the checkbox Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service - this is new in SQL Server 2016. This enables 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.




15. In the Database Engine Configuration dialog box, under the Server Configuration tab,
• Select Windows authentication mode in the Authentication Mode section. If required, you can change it later after the installation is complete.
• Add the currently logged on user to be a part of the SQL Server administrators group by clicking the Add Current User button in the Specify SQL Server Administrators section. You can also add Active Directory domain accounts or security groups as necessary.




In the Data Directories tab, specify the location of the data files, the log files and the backup files.



Also new in SQL Server 2016 is the specific tab for TempDB configuration. You can set the number of tempdb data files, initial size and autogrowth settings of both data and log files as well as their corresponding locations.



Note that, starting with SQL Server 2012, you have the option to store your tempdb database files on a local disk in a WSFC. You can still choose to host the tempdb database on shared storage like the one provided in this example. Should you decide to store tempdb on a local disk, you will get prompted to make sure that all of the nodes in the WSFC contain the same directory structure and that the SQL Server service account has read/write permissions on those folders.




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





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





18. In the Complete dialog box, click Close. This concludes the installation of a SQL Server 2016 FCI.
    

At the completion of a successful installation and configuration of the node, you now have a fully functional SQL Server 2016 FCI. To validate, open the Failover Cluster Manager console and click on SQL Server (MSSQLSERVER) under Roles. Make sure that all dependencies are online.

Also new in SQL Server 2016 FCI is the additional cluster resource named SQL Server CEIP (MSSQLSERVER) or the Local Audit for SQL Server Usage Feedback Collection feature. This additional cluster resource/role inside the SQL Server cluster resource group does not directly impact the SQL Server FCI. By default, failure of this resource does not cause a failover of the entire SQL Server cluster resource group. However, you still need to monitor whether this resource is online or not. A more detailed discussion of this feature will be covered in a future tip.

Although we do have a fully functioning SQL Server 2016 FCI, it is not highly availability at this point in time because the SQL Server binaries are only installed on one of the nodes in the WSFC. To make it highly available, you still have to add the second node of the WSFC to the SQL Server FCI.

In the last part of this series, you will add the second node in the SQL Server FCI and test client application connectivity.

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.
• Read more on the following topics:
• Installing, Configuring and Managing Windows Server Failover Cluster using PowerShell Part 1
• Validating a Windows Cluster Prior to Installing SQL Server 2014
• What's new in Failover Clustering in Windows Server 2016

About the author

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