SQL Server 2012 Failover Cluster Installation – Step by Step Guide – Part 1

Thanks to: http://theadminsguide.net/

Recently i have been preparing some articles that involve step by step guides for installing some applications that require a SQL Server for their databases, that’s when i thought why not also write one about installing a highly available SQL installation which i will eventually use for my lab. So today i am going to walk you through installing a SQL Server 2012 Failover Cluster on top of a Windows Server 2012 R2 Operating System.

Software Requirements

• According to Microsoft’s KB Article, to install SQL Server 2012 on top of Windows Server 2012 R2 you need to have SP1 for SQL Server 2012 installed. so in this article i am going to use a SQL Server 2012 With SP1 installation media.
• After you install SQL Server 2012 SP1, you may experience an issue in which Windows Installer starts repeatedly. This issue is described in the following article in the Microsoft Knowledge Base. Please note that it is mandatory that you install this hotfix after you install SQL Server 2012 SP1. “Windows Installer starts repeatedly after you install SQL Server 2012 SP1“
• .NET Framework.NET 3.5 SP1 is a requirement for SQL Server 2012 when you select Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.
  .NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.
  
• Windows PowerShell SQL Server 2012 does not install or enable Windows PowerShell 2.0; however Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio.
• Virtualization SQL Server 2012 is supported in virtual machine environments running on the Hyper-V role.

SQL Server Failover Cluster Installation options

There are two options to install a SQL Server Cluster:

1. Integrated installation with Add Node
   • Create and configure a single-node SQL Server failover cluster instance. At the completion of a successful configuration of the node, you have a fully functional failover cluster instance. At this time it does not have high-availability because there is only one node in the failover cluster.
   • On each node to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.
2. Advanced/Enterprise installation
   • on each node that will be part of the SQL Server failover cluster, run Setup with Prepare Failover Cluster functionality. This step prepares the nodes ready to be clustered, but there is no operational SQL Server instance at the end of this step.
   • After the nodes are prepared for clustering, run Setup on the node that owns the shared disk with the Complete Failover Cluster functionality. This step configures and completes the failover cluster instance. At the end of this step, you will have an operational SQL Server failover cluster instance.

in this article we will be using the Integrated installation method

Preparing the Servers

So i will be creating a SQL cluster of two nodes, and for that i have prepared 2 Virtual machines with Windows Server 2012 R2 installed. i have already configured a shared storage for the two nodes using Windows Server 2012 iSCSI taregt Server feature. for a detailed steps on how to implement this check this article “Installing and Configuring iSCSI Target Server on Windows Server 2012 R2 – Step by Step“

For your Cluster network it is no more required to have a dedicated NIC for the heartbeat between cluster nodes since Windows Server 2008, heartbeat check would go through any available network, yet make sure you do not have a single point of failure, you can use Network teaming to make sure your network is highly available. in addition, it is highly recommended that you isolate you storage traffic on a separate Network interface.

So now what i am going to do is install Failover Cluster feature on the two nodes. Start by going to Server Manager and click to add features to the server.

Click Next for a Role-based or Feature-based installation

Choose the Local Server

Nothing to Select hereClick to choose Failover Clustering

Add the required features

Start the installation

Now that the installation is completed, repeat the same steps on the second node.

So let’s start and create our cluster by opening the Failover Cluster Manager.

Start by validating your Hardware configuration.

Select all nodes that will be members of this cluster.

According to your requirement, select to run all validation test or specific ones, i am going to run all.

Wait for the tests results, this might take sometime.

Well, i received a warning because i am only using a single network adapter which is considered a single point of failure, but this is fine by me in this lab, so i am going to start creating my cluster.

Click to create a cluster

Choose the nodes to be added to this cluster.

Choose a Cluster Name and a Cluster IP Address.

If you have already configured the required storage choose to add it to the cluster.

Wait for the Cluster to be created.

Make sure that all your resources are online.

From the Failover Cluster Manager check which node is the current owner for the disk , and from there start creating your volumes. i will create 4 Volumes now : one for SQL Database, one for SQL Database Logs, one for the Cluster Quorum and one for MSDTC which i will be talking about right away.

Please note you need to have the disks in Maintenance mode to be able to format them, this can be done from the Cluster Manager

The cluster quorum configuration has a direct effect on the high availability of the cluster, it determines the number of node failures the cluster can sustain to stay online,the cluster will stop running if that number is exceeded, for example, a 3 Node cluster would require 2 of the nodes up and running to work, that is why a Node majority model is recommended for clusters with ODD number of nodes, while in cases of even number of nodes, like in our case of a 2 Node cluster you need an additional object to be counted as a vote in case of a node failure, otherwise the cluster will fail.

Having a Quorum is based on voting algorithm where more than half of the voters must be running and able to communicate with each other.If the number of voters is below the majority, the cluster service stops

A voter can be a Node, a Disk Witness or File Share Witness .It helps ensure that the failover cluster can start properly or continue running when the active cluster membership changes due to a planned or unplanned node shutdown or a connectivity disruption

since we only have 2 nodes in our cluster, the Cluster configuration automatically adds on of the available disks as a Disk witness to ensure a high availability for this cluster. to change this do the following.

Click to configure Quorum settings

Click on  Advanced Quorum Configuration

You can now adjust the quorum votes as required for the cluster to continue running,from this wizard you can exclude any node from the listed here to be a counted vote.

Configure a Disk Witness

Select the Disk you need to assign for this cluster as the disk witness

Confirm your configuration.now let’s run a validation report for the Quorum Configuration. as you can see that with the disk witness online you can sustain a failure up to 1 node.

Clustering MSDTC

To finalize this part of the article we are going to talk about MSDTC, and start configuring it. Before installing SQL Server on a failover cluster, determine whether the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource must be created. If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS “Integration Services”, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

First we need to install MSDTC on both nodes, to do this go to your server manager and open the add roles or features wizard. and choose to add the Application Server Role.

On the Role Service step, select both incoming and outgoing Network Transactions.

Repeat the same steps for the second node, once you’re done go to the Failover Cluster Manager and Click on Configure Role

Choose Distributed transaction Coordinator

Type a Name and Choose an IP Address.

Choose the Storage to be assigned.

Congrats, now you have finished providing High availability for the MSDTC role.

With these configurations we have finished Preparing our servers for the SQL installation, in the second part of this article we will continue to install SQL 2012 Server on the two nodes. See you !