Before we start to configure SQL Server Always On, we need to make sure that we have:

  • One Domain Controller
  • Two Database Servers
  • A Storage that will play the File Share Witness (Witness Quorum)

All Server must be updated with the latest windows updates.

Back to top


Virtual Machines IPs

VM Role IP Address Subnet Mask Subnet
Domain Controller 10.2.1.4 255.255.255.0 BackEnd
SQL Server 1 10.2.1.6 255.255.255. BackEnd
SQL Server 2 10.2.1.5 255.255.255.0 BackEnd

Back to top


Windows Firewall Rules

PURPOSE TCP PORT NOTES
SQL Server 1433 Default TCP Port for SQL Server
Health Probe 59999 TCP Port to be used to control the LB Health Probe
Availability Group Listener 1436 TCP Port who communicates both SQL Server's

Back to top


Domain Controller

Install the required roles

In Server Manager we select Manage-Add Roles and Features and in the Server Roles tab we select Active Directory Domain Services, DNS Server

Server Configuration as Domain Controller

At the top right of the Server Manager, a warning message appears to promote the Server as a Domain Controller.

For more details, please follow the links:

Back to top


SQL Server

Install the required roles

In Server Manager select Manage - Add Roles and Featuresand in the Server Roles tab, select Failover Clustering

Back to top


Configuring the Failover Cluster on the Server

In Server Manager, select Tools - Failover Cluster Manager.

In the left pane, right-click Failover Cluster Manager - Create a Cluster

In the Create Cluster Wizard, create a cluster node with the parameters shown in the following table:

PAGE SETTINGS
Before You Begin Use defaults
Select Servers Type the name of SQL Server in the server name field and click Add.
Validation Warning Select No. I do not require support from Microsoft for this cluster, so I do not want to run validation tests. Click Next, and the Cluster creation process proceeds.
Access Point for Administering the Cluster Cluster name is listed, eg SQLCluster, in the Cluster Name field.
Confirmation uses the default unless you use Storage Spaces.

Once the above steps are completed, then go to Failover Cluster Manager, in the Cluster Core Resources field, right-click Server Name "SQLCluster" -properties.

In the new form that opens, we change the value in the Static IP Address field to 10.2.1.15 and OK.

Then right-click on SQL Cluster and choose to Bring Online.

In the next step, right-click Nodes-Add Node ...

To add the 2nd Node (SQL Server VM), follow the steps of the Add Node Wizard with the settings shown in the table below.

PAGE SETTINGS
Before You Begin Use defaults
Select Servers Type SQL Server name on the server name and click Add.
Validation Warning Choose No. I do not require support from Microsoft for this cluster, and therefore do not want to run the validation tests. Click Next, and the Cluster creation process proceeds.
Confirmation We use the default unless we use Storage Spaces.

Back to top


Add clustered quorum file share

To add a quorum file share cluster, follow these steps:

FromServer Manager-Tools, we open Computer Management

Click on Shared Folders, right-click on Shares-New Share ...

We create a new shared folder and give Full Control permissions to the SQL Cluster user

Back to top


Quorum cluster customization

In Failover Cluster Manager, right-click on the cluster, More Actions - Configure Cluster Quorum Settings ...

In the Configure Cluster Quorum Wizard, follow these steps,

PAGE SETTINGS
Before You Begin Use defaults
Select Quorum Configuration Option Select the quorum witness, You can add or change the quorum witness. The cluster determines the other quorum management options.
Select Quorum Witness Configure a file share witness, Adds a quorum vote of the file share witness
Configure File Share Path \\10.2.0.4\WitnessQuorum
Confirmation Confirm the settings
Configure Cluster Quorum Settings Since there is no problem in customization, the Wizard is completed.

 

Once the above steps (and for the 2 SQL Serves) have been completed, we need to have the following image in Failover Cluster Manager:

      

Back to top


Enable Availability Groups in the SQL Server service

We open SQL Server Configuration Manager, right-click the SQL Server service, Properties.

Click on the Always Οn High Availability tab and check Enable Always Οn Availability Groups {The same step will be on the 2nd SQL Server}

Create Availability Group on SQL Server

We open the SSMS SQL Server Management Studio and right-click on the Always Οn High Availability list-New Availability Group Wizard ...

PAGE SETTINGS
Specify Options Type Availability Group name, for example, AG
Select Databases Select the base or bases that will participate in the Availability Group
Specify Replicas Tab Replicas :
  • Click Add Replica.., add the 2nd SQL Server to join the Availability Group.
  • Check Automatic Failover (Up to 3), Synchronous Commit(Up to 3)
Select Data Synchronization Check Full, and select the shared directory that is accessible from both 2 Servers.
Validation At this point there will be ONLY a Warning about the listener configuration because it has not yet been configured.
Results Click Close

Once the above steps have been completed, we almost have done.


Back to top

Create Availability Group Listener

Initially, we need to create an Azure Load Balancer by following these steps:

Create Azure Load Balancer

SETTING VALUE
Name AG - Listener LB
Type Internal
Virtual Network Select the Virtual Network
Subnet We select the subnet belonging to SQL Servers, e.g Back End
IP address assignment Give an IP that is not used by any VM and should be static, e.g10.2.1.14
Subscription Select the subscription that VMs belong
Resource Group Select the Resource Group
Location Select the Data Center where the resources are hosted

Azure backend pool Configuration

SETTING VALUE
Name AG-Listener Backend Pool
Associated to Select the Availability Set where SQL Server VMs belong
+Add a target network IP configuration Select both SQL Server VMs

Azure Health Probe Customization

SETTING VALUE
Name AG-Listener Health Probe
Protocol TCP
Port 59999
Interval 5 seconds
Unhealthy threshold 2 consecutive failures

Create Azure Load balancing rules

SETTING VALUE
Name AG-Listener LB Rules
IP Version IPv4
Frontend IP address 10.2.1.14 (Load Balancer Front End)
Protocol TCP
Port 1436
Backend port 1436
Backend pool AG-LIstenerBackendPool (2 virtual machines)
Health probe AG-ListenerHealthProbe(TCP:59999)
Session persistence None

Back to top


Configure the Cluster to use the Load Balancer IP

Right-click on the cluster, Add Resource- the Client Access Point

And the New Resource Wizard records the following:

Client Access Point 10.2.1.14
Confirmation Confirm the parameters
Configure Client Access Point Confirm the parameters

Once the Wizard finishes, right-click the listener and choose to Bring Online.

Back to top


Parameters on the Cluster via PowerShell

Execute the following PowerShell script on one of the 2 database servers.

$ClusterNetworkName = "Cluster Network 1" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "IP Address 10.2.1.14" # the IP Address resource name
$ILBIP = “10.2.1.14” # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
[int]$ProbePort = 59999
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"=$ProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}


After running the above script, in the Availability Group Listeners list, AG-Listener will appear.

Back to top