Overview
Windows Server Failover Clustering is a high-availability and disaster recovery solution designed to increase the uptime of SQL Server instances. A cluster is a group of independent servers, called nodes, that work together to increase the availability of applications and services that run on the cluster. One is identified as the active node, on which a SQL Server instance is running the production workload, and the other is a passive node, is installed but not running. If the SQL Server instance on the active node fails, the passive node becomes the active node and begins to run the SQL Server production workload with minimal failover downtime. This process is known as failover.
What Clustering Can Do
Clustering is designed to improve the availability of the physical server hardware, operating system, but excluding the shared storage. Should any of these aspects fail, the SQL Server instance fails over. The other node in a cluster automatically takes over the failed SQL Server instance to reduce downtime to a minimum.
Additionally, the use of a Windows Failover Cluster can help reduce downtime when you perform maintenance on cluster nodes. For example, if you need to update hardware on a physical server or install a new service pack on the operating system, you can do so one node at a time.
Terms and Definitions
Failover cluster instance: An instance of a Windows service that manages an IP address resource, a network name resource, and additional resources that are required to run one or more applications or services. Clients can use the network name to access the resources in the group, similar to using a computer name to access the services on a physical server. However, because a failover cluster instance is a group, it can be failed over to another node without affecting the underlying name or address.
Node: A Microsoft Windows Server system that is an active or inactive member of a server cluster.
Cluster resource: A physical or logical entity that can be owned by a node, brought online and taken offline, moved between nodes, and managed as a cluster object. A cluster resource can be owned by only a single node at any point in time.
Resource group: A collection of cluster resources managed as a single cluster object. Typically, a resource group contains all of the cluster resources that are required to run a specific application or service. Failover and failback always act on resource groups.
Resource dependency: A resource on which another resource depends. If resource A depends on resource B, then B is a dependency of A.
Network name resource: A logical server name that is managed as a cluster resource. A network name resource must be used with an IP address resource.
Preferred owner: A node on which a resource group prefers to run. Each resource group is associated with a list of preferred owners sorted in order of preference. During automatic failover, the resource group is moved to the next preferred node in the preferred owner list.
Possible owner: A secondary node on which a resource can run. Each resource group is associated with a list of possible owners. Resource groups can fail over only to nodes that are listed as possible owners.
Quorum mode: The quorum configuration in a failover cluster that determines the number of node failures that the cluster can sustain.
Forced quorum: The process to start the cluster even though only a minority of the elements that are required for quorum are in communication.
Hardware and Software Requirements
The following are the hardware and software requirements for installing the SQL failover cluster.
● A fault tolerant domain controller (each controller installed on a separate physical machine)
2 Windows 2012 R2 servers each hosted on a separate physical machine
At least 50GB of storage per server
Each server should belong to a separate subnet and have additional IPs defined for the network controller
.Net 3.5 installed on each server
● A single SQL 2014/2016 Standard Edition installer and license
● A fault tolerant shared/SAN/NAS storage (min. size 50GB, recommended size 1TB)
Installation Checklist
The following is a checklist of the procedures that need to be taken in order to create the Windows and SQL cluster.
Domain Controller (DC) and Windows Cluster Configuration:
● Define the DC instance, if you don't have one.
● Designate computer names with DNS.
● Create a domain user with domain controller permissions to update the DNS of these
as follows: CC_CLUSTER (or choose your own name) – for example 10.33.16.100
● Define the SQL cluster virtual name and IP as follows: DEFAULTSQL (or choose your own name) – for example 10.33.16.101
● Grant CC_CLUSTER full permissions on DEFAULTSQL using advanced properties (ALT+M).
● If using file share and a quorum witness file on the DC, create two shares on the DC with permissions to the SQL domain user.
Cluster nodes:
● Define the Windows cluster. Set the cluster IP, for example: 10.33.16.100
● In the cluster, set the File Share Witness file to be directed at the shared FS.
● Perform custom verification of cluster omitting storage.
● After verification succeeds, run the SQL failover installation.
● Use a domain user for installing and for running the SQL server (this is necessary to access file/folder shares and change the IP in the DNS).
● Set up temp folders to the local server; all other locations should point to shared storage.
● Upon first server installation, test the connectivity to the DB.
● Install an additional SQL server on an additional node/server, with an added node to the failover cluster option.
● Provide passwords where needed.
● Test the connectivity.
