MSSQL Server Availability Group(AG) : Fail-over Clustering with Secondary Replica

To create Availability Group(AG) Fail-over SQL Server Cluster with secondary replica we need 4 machines/VMs which are as follows

1. Domain Controller:
   1. Machine Name: DC
   2. 192.168.1.71
   3. Domain Name: TEST.COM
2. Node1:
   1. Machine Name: HClust01
   2. IP:  192.168.1.81
3. Node2:
   1. Machine Name: HClust02
   2. IP: 192.168.1.82
4. Node3:
   1. Machine Name: HClust03
   2. IP: 192.168.1.83

To setup MSSQL Server Cluster we need to have WSFC – Windows Server Failover Clustering, FCI – Failover Cluster Instance (2-node) and AG – Availability group (2-node auto failover and 3^rd node as Secondary Replica) is based on the following 4 phases. The above mentioned 3 nodes must be joined to domain i.e. all the nodes in WSFSC must be on same domain and same version of SQL Server and Operating System.

1. Domain Controller
2. WSFC
3. FCI
4. AG

Note: FCI is based on 2 nodes with shared disk i.e. 2 nodes is having 1 SQL Server AG is having 2 SQL Server i.e. data disk is not shared

Phase 1: Setup Domain Controller

First we need to setup Domain Controller (DNS & Active Directory). We have assigned the following configuration during setup

• Machine Name: DC
• 192.168.1.71
• Domain Name: TEST.COM

Active Directory

Phase 2: Install & Configure Windows Server Fail-over Clustering (WSFC)

First we need to create Windows Server Fail-over Cluster on Node 1. We have setup following during configuration wizard. Moreover we need to locate shared disk as Quorum disk which is shared among all the nodes of the cluster

• Windows Cluster Name: TESTCLUST
• Cluster IP: 192.168.1.85
• Shared Disk:2
  1. Quorum: 2 GB (To Store System Status)
  2. Data: 50 GB (To Store Actual Data)

After these configurations check the fail-over between 2 nodes at OS level.

Phase 3: Install & Configure MSSQL Server Cluster-FCI

Install database failover cluster by running MSSQL Server 2012 Setup and select failover cluster option. We have done this on Cluster on Node 1 and have done following setup during configuration wizard. Then connect Node 2 and run MSSQL Server 2012 Setup now select add cluster node option and proceed with the wizard.

Database MSSQL Server Cluster

Cluster Name: CLUSTERTEST

Cluster IP: 192.168.1.90

WSFC Role: SQL Server (MSSQL Server)

The above cluster is based on the following 2 nodes

Node1: Machine Name: HClust01 ||| IP: 192.168.1.81

Node2: Machine Name: HClust02 ||| IP: 192.168.1.82

After this setup check & confirm the database fail-over between these 2 nodes (SQL Level).

Cluster Dependency Report

SQL Server Connection

Phase 4: Install new SQL Server & Configure AG – Availability Group

Install a new standalone MSSQL Server 2012 and do the default configuration.

Add this node to WSFC (i.e. the windows cluster that created earlier) and restart the instance. Now configure the availability group from MSSQL Server management studio and Setup the database as failover secondary replicas as 3rd node for the database cluster. We need to create a shared location that is accessible to all the nodes. Database backup on primary need to be done that will be restore to Secondary AG Node to start the synchronization process. The synchronization mode can be either Asynchronous or Synchronous.

The details for this cluster are as follows: Keep in mind that this failover is done manually

Database MSSQL Server Cluster

Cluster Name: CLUSTER

IP Address: 192.168.1.173

SQL Server Instance Name: AGSecondary

WSFC Role: AG

The above cluster is based on the following node

Node3: Machine Name: HClust03 ||| IP: 192.168.1.83

After this setup check & confirm the database fail-over between these 2 clusters (AG Group)

Manual Fail-over for 3^rd Node (SQL Server Level):

Under the tab of AlwaysOn High Availability right click the AG, select Fail-over and proceed with the Fail Over Availability Group: AG widget. This is for Manual Fail-over for both to come and leave from AG Group (In our case 3^rd node).