Configure SQL server fail-over cluster and Always On in multi-subnet environment-Part 1

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

InterServer Web Hosting and VPS

Lets Begin: We are going to start a series of blogs on configuring SQL server fail-over cluster and AG in multi-subnet environment.

This is the 1st part of the series from where we will take you through the overall setting up this requirement .

Windows server 2012 R2 and SQL sever 2014 are taken as an example in this series.

  • IMP Note:- Multi-subnet windows cluster has been supported from Windows server 2008 Onward but for SQL, multi-subnet fail-over cluster started from version:- SQL server 2012 onward.
  • We have considered the setup for multi-subnet as per below example.
  • California–> In same subnet–>Same(1st) Datacenter–>SQL fail-over cluster will be setup between them in same subnet.
  • New York–> In Different subnet–>Different DC(2nd)–> AG will be setup between 1st and 2nd DC i.e. multi-subnet network.

Why to install Failover Clusters in Windows Server

Failover Cluster is a group of Servers that work together to increase the availability and scalability. 

Let’s imagine that you have a single HYPER-V Host with 10 Virtual Machines.

One day the HYPER-V Host is dead for an unknown reason. What you will do in this scenario?

So the only one reason to use Failover Clusters is for the High Availability.

We will now discuss about pre-requisites for Windows and SQL cluster installation.

 Windows & SQL Server Pre-requisites

  • Two or more compatible servers: You need hardware that is compatible with each other, highly recommended to use same type of hardware when you are creating a cluster.
  • Two network cards on each server, one public network (from which we usually access Active Directory) and a private for heartbeat between servers. This is actually an optional requirement since using one network card is possible but not suitable in almost any environment.
    When we are using iSCSI protocol for our shared storage Microsoft recommends three network cards on each host: Public network, private, and one dedicated to iscsi communication from servers to the storage
  • Compatible Windows Server Version and Editions for hosts, which will be part of the cluster.
  • All hosts must be member from an Active Directory domain. To install and configure a cluster we need a Domain Admin account& Domain Account for SQL Services, which need not be Domain-Admin account that is included in the local Administrators of each host.
  • Windows Cluster to have unique Virtual name & IP Address.

Proper rights to create Fail-over Cluster

A CNO is automatically created during cluster setup. When the administrator creates a failover cluster and configures clustered services or applications, the Create Cluster Wizard creates all the Active Directory computer accounts the failover cluster requires and gives each account specific permissions. The wizard also creates a computer account for the failover cluster itself; this account is called the cluster name object.

The account used to create the cluster must have administrator rights on the computers that are becoming part of the new cluster and the Create Computer Objects permission on the container where computer accounts are created in the domain. This is because the wizard that creates failover clusters creates the computer account for the new cluster and gives that account the necessary permissions, such as creating computer objects in the domain’s computer account container (which lets the cluster create additional computer accounts for any clustered services or applications).

We must grant the permissions “Read all properties” and “Create Computer objects” to the CNO via the container.

Steps for configuring the account for the person who installs the cluster

Membership in the Domain Admins group, or equivalent, is the minimum required to complete this procedure. In addition, your account must be in the local Administrators group on all of the servers that will be nodes in the failover cluster.

Now, there are numerous organizations that do not have Domain Administrators create Failover Clusters. So the question becomes, exactly what is the “equivalent” rights that are needed for this user. Below are the rights that are needed in the OU.

o Create Computer Objects

o Read All Properties

With the above rights, Cluster Validation will pass and the Cluster object can be created.

Follow this for more info if any.

Requirements for SQL Server 2014 Failover Cluster

  • IP address and server name for each nodes of the cluster
  • IP address for virtual server & MSDTC Services
  • SQL Server installation file location (Setup) preferable on the LUNS connected to both the servers
  • Domain service account added in Local Administrator on Both the Servers for SQL Services.
  • SQL Directory for SQL installation file and database location
  • Instance name and port no
  • Authentication mode and collation setting
  • Windows installer 4.5 and .Net framework 3.5 SP1

IP&Datacenter details/purpose

We have taken below IP for demo purposes, its purposes and Geographical location are detailed in below chart.

Go through these details to understand and get the clarity of this overall tutorial/series on SQL cluster and AG in multi-subnet.

Click to get its part-2

Hope this helps!

We should not fret for what is past, nor should we be anxious about the future; men of discernment deal only with the present moment.