• 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.

Virtual Machines IPs

VM RoleIP AddressSubnet MaskSubnet
Domain Controller10.2.1.4255.255.255.0BackEnd
SQL Server 110.2.1.6255.255.255.BackEnd
SQL Server 210.2.1.5255.255.255.0BackEnd

Windows Firewall Rules

PURPOSETCP PORTNOTES
SQL Server1433Default TCP Port for SQL Server
Health Probe59999TCP Port to be used to control the LB Health Probe
Availability Group Listener1436TCP Port who communicates both SQL Server’s

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 ServicesDNS 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:

SQL Server

Install the required roles

In Server Manager select Manage – Add Roles and Features and in the Server Roles tab, select Failover Clustering

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:

 

PAGESETTINGS
Before You BeginUse defaults
Select ServersType the name of SQL Server in the server name field and click Add.
Validation WarningSelect 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 ClusterCluster name is listed, eg SQLCluster, in the Cluster Name field.
Confirmationuses 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.

 

PAGESETTINGS
Before You BeginUse defaults
Select ServersType  SQL Server name on server name and click Add.
Validation WarningChoose  No. I do not require support from Microsoft for this cluster, and therefore do not want to run the validation testsClick Next, and the Cluster creation process proceeds.
ConfirmationWe use the default unless we use Storage Spaces.

Add clustered quorum file share

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

From Server 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

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,

PAGESETTINGS
Before You BeginUse defaults
Select Quorum Configuration OptionSelect the quorum witness, You can add or change the quorum witness. The cluster determines the other quorum management options.
Select Quorum WitnessConfigure a file share witness, Adds a quorum vote of the file share witness
Configure File Share Path\\10.2.0.4\WitnessQuorum
ConfirmationConfirm the settings
Configure Cluster Quorum SettingsSince 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:

 

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 …

PAGESETTINGS
Specify OptionsType Availability Group name, for example, AG
Select DatabasesSelect 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.
ValidationAt this point there will be ONLY a Warning about the listener configuration because it has not yet been configured.
ResultsClick Close

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

Create Availability Group Listener

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

Create Azure Load Balancer

SETTINGVALUE
NameAG – ListenerLB
TypeInternal
Virtual NetworkSelect the  Virtual Network
SubnetWe select the subnet belonging to SQL Servers, ie BackEnd
IP address assignmentGive an IP that is not used by any VM and should be static, e.g 10.2.1.14
SubscriptionSelect the subscription that VMs belong
Resource GroupSelect the Resource Group
LocationSelect the  Data Center where the resources are hosted

Azure backend pool Configuration

SETTINGVALUE
NameAG-ListenerBackendPool
Associated toSelect the Availability Set where SQL Server VMs belong
+Add a target network IP configurationSelect both SQL Server VMs

Azure Health Probe Customization

SETTINGVALUE
NameAG-ListenerHealthProbe
ProtocolTCP
Port59999
Interval5 seconds
Unhealthy threshold2 consecutive failures

Create Azure Load balancing rules

SETTINGVALUE
NameAG-ListenerLBRules
IP VersionIPv4
Frontend IP address10.2.1.14 (LoadBalancerFrontEnd)
ProtocolTCP
Port1436
Backend port1436
Backend poolAG-LIstenerBackendPool (2 virtual machines)
Health probeAG-ListenerHealthProbe(TCP:59999)
Session persistnceNone

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 Point10.2.1.14
ConfirmationConfirm the parameters
Configure Client Access PointConfirm the parameters

 

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

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 isthe staticIP address forthe load balancer you configured inthe 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.