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

By:   |   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. New SQL Server failover cluster installation

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

  5. SQL Server Failover Cluster product key

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

  7. SQL Server Failover Cluster license terms

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

  9. SQL Server Failover Cluster global rules

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

  11. microsoft update for SQL Server Failover Cluster

    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.

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

    SQL Server Failover Cluster rules

  14. In the Feature Selection dialog box, select the following components Database Engine Services and Client Tools Connectivity. Click Next.
  15. Note that SQL Server Management Studio is no longer included in the SQL Server 2016 installation media and have to be downloaded separately.

    SQL Server Failover Cluster feature selection

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

  17. SQL Server Failover Cluster feature rules

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

  19. SQL Server Failover Cluster instance configuration

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

  21. SQL Server Failover Cluster cluster resource group

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

  23. SQL Server Failover Cluster cluster disk selection

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

  25. SQL Server Failover Cluster cluster network

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

    SQL Server Failover Cluster server configuration

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

    SQL Server Failover Cluster server configuration

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

    SQL Server Failover Cluster data directories

    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.

    SQL Server Failover Cluster database engine configuration

    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.

    SQL Server Failover Cluster failover cluster

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

  30. SQL Server Failover Cluster feature configuration rules

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

  32. SQL Server Failover Cluster ready to install

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

  34. SQL Server Failover Cluster complete

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.

SQL Server Failover Cluster failover cluster manager

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


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




Thursday, July 9, 2020 - 1:22:14 PM - bass_player Back To Top (86113)

Have a look at the list of tips in this list

SQL Server Availability Group Tips

https://www.mssqltips.com/sql-server-tip-category/143/availability-groups/


Wednesday, July 8, 2020 - 8:28:28 PM - elrusdi Back To Top (86109)

Thanks for the article step by step is for FCI  please create on Sql Server AG also and is that problem where to sql install first on node1 or on Node2. Because I had error when install swl server on node 2 after install sql server on node 1. It said that make sure all node can connevted. But in Failover cluster all node is up and firewallmis off on all node why it can note connected.


Saturday, June 10, 2017 - 10:30:53 AM - bass_player Back To Top (57104)

 Yes, refer to thiss tip for reference

https://www.mssqltips.com/sqlservertip/4840/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster--part-4/


Thursday, June 8, 2017 - 5:41:34 PM - Miguel Back To Top (56982)

Hi, you will run the installation process on the first node.

In the second node, you will run the instalation of SQL Server too?.


Best regards,


Wednesday, May 31, 2017 - 9:35:18 PM - bass_player Back To Top (56383)

 Robert,

Are you referring to SQL Server Availability Groups? A webcast recording is available here.

https://www.mssqltips.com/sql-server-video/105/sql-server-high-availability-with-alwayson/


Wednesday, May 31, 2017 - 2:38:28 PM - robert rogers Back To Top (56364)

 Awesome write up... I need to find your always on failover cluster write up.

 















get free sql tips
agree to terms