By: Edwin Sarmiento | Last Updated: 2017-04-20 | Comments (4) | Clustering
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?
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.
- 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 New SQL Server failover cluster installation
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, 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.
- 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.
- In the Feature Selection dialog box, select the following components Database Engine Services and Client Tools Connectivity. Click Next.
- 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.
- 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.
- 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.
- 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
2 of this tip series.
- 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.
- 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.
- In the Database Engine Configuration dialog box, under the Server Configuration tab,
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.
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.
Note that SQL Server Management Studio is no longer included in the SQL Server 2016 installation media and have to be downloaded separately.
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.
- 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.
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.
- 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:
Last Updated: 2017-04-20
About the author
View all my tips