How to install clustered SQL instance Part 1

So let’s take a look on how to install Clustered SQL instance from scratch!

I’m about to start a new series of articles about the installation of a Failover Cluster SQL Instance. This is an environment I will be using primarily for testing in my lab hence with no intent to follow the strictest best practices when it comes down to hardening the infrastructure providing SQL Services. This wants to be a quick tutorial on how to simply proceed with the deployment. I would always recommend though to take a look at the official SQL documentation to adapt this steps to the personal requirements.

For an easier reading (and to cut down on the number of screenshots!) I have organised this guide in 3 parts:

• Part 1 – Create a Windows Cluster to support install Clustered SQL deployment
• Part 2 – Install the first SQL node of the clustered SQL instance
• Part 3 – Add a node to the SQL clustered instance and test failover

So here we go with the first part about creating a SQL Cluster.

For my lab environment I decided to use a 2 nodes cluster based on Windows Server 2012 R2. These two nodes will also be the same serving the failover clustered SQL instance. Ideally I would like to have separate volumes hosting separate SQL components like for example:

It’s quite a big number of drives and for obvious reasons with the exclusion of P drive all the remaining ones are iSCSI drives connected to both nodes participating to the cluster. For more information on how to create and mount this drives this article comes handy.

So having a clustered SQL installation in mind this is the shopping list:

• Create the shared drives
  • B disk for root directory, system databases and backups
  • T disk for TempDB (data and log)
  • Q disk for Quorum
  • S disk for User Databases data
  • L disk for User Databases logs
• Create a DNS Host (A) Record for SQL Cluster name
  • My lab will use SQLCLU-2014 with IP 10.254.6.252
• Create a Computer Object in Active Directory with SQL Cluster name
  • My lab will use SQLCLU-2014
• Create a DNS Host (A) Record for Windows Cluster
  • My lab will use WINCLU-2012 with IP 10.254.6.253
• Create a Computer Object in Active Directory with Windows Cluster name
  • Actually this will be automatically provisioned by the WSFC during the installation wizard

To make this happen we need to first install the Windows Failover Cluster Service Feature so we can create a Windows Cluster which will support an install Clustered SQL as a Windows Cluster Role!

Install the Windows Failover Cluster feature

First of all from the Windows Server Manager let’s add the role of Failover Clustering as per screenshots below. The same role has to be installed on all nodes participating to the cluster. For this article the installation in my lab will be executed on SQL2014A and SQL2014B nodes respectively.

From the Feature list let’s select the Failover Clustering option. This is an essential component as the NTFS file system doesn not natively support cluster aware applications. With this feature enabled we can control this aspect and a lot more.

As soon as we check this component the wizard shows the Failover Cluster Management Tools and Failover Cluster Module for Windows Powershell to install. Let’s make sure the pertinent checkbox to include such management tools is selected.

Let’s review the install components and click on install to progress.

At this point before proceeding it is recommended to “Validate Configuration” by opening the Cluster Manager snap-in.  A quicker way is to run with the command

“CLUAdmin.msc“

This will run an extensive list of tests to check if all components are fit for the purpose. It might take some time depending on the components and also on the level of Software Drivers and Updates installed.

It is recommended to start with a full scan with all tests the very first time to identify potential issues. Afterwards it is possible to select specific ones where issues (warning or errors) are found.

Assuming no big warnings or errors are detected (besides the common ones about network and disk redundancies) we can start creating our Windows Cluster which will host the install Clustered SQL instance as a Role.

Let’s add the intended nodes forming the Windows Cluster and make sure FQDN name resolution is working both ways with forward and reverse name resolution. This step is crucial.

Depending on situations we might want to run the Validation check. It might take some time to complete but is very beneficial in order to understand where potential problems are. Also this can help expedite troubleshooting with Microsoft Support in case of issues in a Production environment.

Let’s start the validation test.

Let’s go for all tests as this is a first time run.

Let’s review the nodes name and continue.

The validation test is now running by probing the physical and logical configuration of the cluster.

And will provide a final output we can use as a report to understand potential configuration issues.

Create the Windows Cluster

Now that all components have been scanned and cleared we are ready to create a cluster by clicking on “Create Cluster” link from the right side panel will start the wizard. Let’s specify the intended name for the Windows Cluster as this will be created as a user object in Active Directory (so let’s make sure same name does not exist yet!).

Also as per previous requirements a DNS Host (A) Record must exist in both forward and reverse zone. Even though it is not a requirement I suggest using a static IP address rather than DHCP. FQDN name resolution is fundamental for the Cluster components to work seamlessy.

A succesful install Clustered SQL instance requires that also the object name created in Active Directory is working as expected from a name resolution point of view with FQDN names and associated IP Address.

So before proceeding I would highy recommend to test both “VIP” names for correct name resolution as lots of components internally use these ones rather IP Addresses.

Let’s review and confirm before amending changes to create the cluster.

The Windows Cluster is now created with the settings as per screenshot below with a pretty much standard configuration using Node and Disk Majority.

It’s now time to grant Full permissions to the Windows cluster Computer object to the SQL Cluster Computer object in Active Directory . To do this from the Advanced View  let’s select the SQLCLU-2014 properties and add Full permissions to the WINCLU-2012 as per screenshots below.

Let’s grant the Windows Cluster object “Full Control” to the SQLClu-2014 object name in Active Directory as per screenshot below.

This concludes the first part to install clustered SQL instance. Next step is to install the first SQL node of the cluster.