SQL Server: Failover Clustering and Always On Availability Groups
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.
Database Minimum Requirements
We also must be sure that the databases meet the following requirements:
- Availability groups must be created with user databases. Systems databases can’t be used.
- Databases must be read-write. Read-only databases aren’t supported.
- Databases must be multi user databases.
- Databases can’t use the AUTO_CLOSE feature.
- Databases must use the full recovery model, and there must be a full backup available.
- A given database can only be in a single availability group, and that database can’t be configured to use database mirroring.
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 |
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 |
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:
- https://blogs.technet.microsoft.com/canitpro/2017/02/22/step-by-step-setting-up-active-directory-in-windows-server-2016/
- https://www.petenetlive.com/KB/Article/0001262
- https://www.tactig.com/promote-windows-server-domain-controller/
SQL Server
Install the required roles
In the 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:
| 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 click 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. |
Add clustered quorum file share
To add a quorum file share cluster, follow these steps:
From the Server Manager - Tools, 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,
| 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:
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 :
|
| 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.
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 |
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.
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.
Database minimum requirement :
- Availability groups must be created with user databases. Systems databases can’t be used.
- Databases must be read-write. Read-only databases aren’t supported.
- Databases must be multiuser databases.
- Databases can’t use the AUTO_CLOSE feature.
- Databases must use the full recovery model, and there must be a full backup available.
- A given database can only be in a single availability group, and that database can’t be configured to use database mirroring.