Deployment Highlight

How to install clustered SQL instance Part 1

Windows Server Fail over Cluster

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:

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:

Purpose

Mounted Drive

Local drive for SQL installation

P

Shared drive for Quorum disk

Q

Shared drive for root directory

B

Shared drive for system databases

B

Shared drive for user Database files

S

Shared drive for user Database logs

L

Shared drive for TempDB

T

Shared drive for Backups

B

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.

domalab.com install clustered SQL add feature

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.

domalab.com install clustered SQL WFCS

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.

domalab.com install clustered SQL WFCS tools

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

domalab.com install clustered SQL feature confirmation

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.

domalab.com install clustered SQL cluadmin

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.

domalab.com install clustered SQL create cluster

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.

domalab.com install clustered SQL validation

Let’s start the validation test.

domalab.com install clustered SQL validate configuration

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

domalab.com install clustered SQL run all test

Let’s review the nodes name and continue.

domalab.com install clustered SQL validation confirmation

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

domalab.com install clustered SQL validating

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

domalab.com install clustered SQL inventory

 

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.

domalab.com install clustered SQL access point

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

domalab.com install clustered SQL confirmation

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

domalab.com install clustered SQL summary

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.

domalab.com install clustered SQL Ad advanced features

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

domalab.com install clustered SQL AD permissions

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

About the author

Michele Domanico

Passionate about Virtualization, Storage, Data Availability and Software Defined Data Center technologies. The aim of Domalab.com is sharing with the Community the knowledge and experience gained with customers, industry leaders and like minded peers. Always open to constructive feedback and new challenges.

Add Comment

Click here to post a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Browse articles

October 2018
M T W T F S S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  

Articles by Category

Archives

%d bloggers like this: