[AZURE] Procedure for creating an MSSQL Always On Cluster on Azure

SQL Server Always On Availability Groups provide high availability, disaster recovery, and read-scale capabilities for SQL Server. When deployed on Azure VMs (IaaS), AGs combine SQL Server’s native HA/DR features with Azure networking, storage, and load balancing. This guide discuses the procedure for creating an MSSQL Always On Cluster on Azure. Please, see “ADK|WinPE|MDT: Deploy Windows with WDS“, and What are the differences between dnf and apt package managers?

You can take a look at the prerequistes. The prerequisites are as follows below, and you are required to have at least two servers on Azure with the following characteristics:

• Be part of the same resource group
• Be part of different Availability Zones or Availability Sets
• Failover Clustering feature installed
• Windows Firewall disabled

Also, see “how to install Endpoint Configuration Manager on HyperV VM, How to modify Windows 11 Taskbar via Intune and GPO, and “Install Windows Admin Center in an unattended mode using a self-signed certificate“.

Install feature for MSSQL Always On Cluster

Use the command below to Install the feature for MSSQL Always On Cluster

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

Add the static record to the root zone

Add-DnsServerResourceRecordA -Name "yourvm instance name" -ZoneName "you domain" -IPv4Address "10.10.10.1" -AllowUpdateAny

Create Storage Pool

Please, follow the steps below to create a Storage Pool. The “Physical Allocation”: Automatic

Create Virtual Disk:
– Storage pool Layout: Simple
– Provisioning: Fixed
– Size: Maximum
– Disable auto AutomaticClustering:
– Create partition “Disk Management”
– File system: NTFS
– Allocation Unit size: 64KB

$name="DB04"
$label = "DATA"
$stp_name="STP_$($label)$($name)"
$vd_name="VD_$($label)$($name)"
$sss=Get-StorageSubSystem | select FriendlyName
$disks=$null
$disks=Get-StoragePool -IsPrimordial $true | Get-PhysicalDisk | Where-Object {$_.CanPool -eq $True -and $_.Size -eq "1TB"}
$storage=New-StoragePool –FriendlyName $stp_name –StorageSubsystemFriendlyName $sss.FriendlyName –PhysicalDisks ($disks)
$vd= New-VirtualDisk –StoragePoolFriendlyName $stp_name –FriendlyName $vd_name -ProvisioningType Fixed –ResiliencySettingName Simple –UseMaximumSize
$vd | Get-Disk | Initialize-Disk –Passthru | New-Partition -DriveLetter 'F' –UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel $label -Confirm:$false

$label = "LOG"
$stp_name="STP_$($label)$($name)"
$vd_name="VD_$($label)$($name)"
$sss=Get-StorageSubSystem | select FriendlyName
$disks=$null
$disks=Get-StoragePool -IsPrimordial $true | Get-PhysicalDisk | Where-Object {$_.CanPool -eq $True -and $_.Size -eq "512GB"}
$storage=New-StoragePool –FriendlyName $stp_name –StorageSubsystemFriendlyName $sss.FriendlyName –PhysicalDisks ($disks)
$vd= New-VirtualDisk –StoragePoolFriendlyName $stp_name –FriendlyName $vd_name -ProvisioningType Fixed –ResiliencySettingName Simple –UseMaximumSize
$vd | Get-Disk | Initialize-Disk –Passthru | New-Partition -DriveLetter 'L' –UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel $label -Confirm:$false

At this point, you can begin with the Microsoft procedure. I will copy it faithfully and add notes in red, as well as examples for any steps that are unclear:

Please, see How to download and install the Windows ADK Patches, and Unable to edit MDT XML unattended file: Could not load file.

Create the cluster

Connect to the SQL Servers and open Failover Cluster Manager. In the left pane, right-click Failover Cluster Manager, and then click Create a Cluster.

In the Create Cluster Wizard, create a one-node cluster by stepping through the pages with the settings in the following table:

Page	Settings
Before You Begin	Use defaults
Select Servers	Type the first SQL Server name in Enter server name and click Add.
Validation Warning	Select No. I do not require support from Microsoft for this cluster, and therefore do not want to run the validation tests. When I click Next, continue Creating the cluster.
Access Point for Administering the Cluster	Type a cluster name, for example SQLAGCluster1 in Cluster Name.
Confirmation	Use defaults unless you are using Storage Spaces. See the note following this table.

Set the Windows server failover cluster IP Address

In Failover Cluster Manager, scroll down to Cluster Core Resources and expand the cluster details. You should see both the Name and the IP Address resources in the Failed state. The IP address resource cannot be brought online because the cluster is assigned the same IP address as the machine itself, therefore it is a duplicate address.

It may not be failed and appear online; proceed anyway with the IP change as described in the following steps.

Right-click the failed IP Address resource, and then click Properties.

Select Static IP Address and specify an available address from the same subnet as your virtual machines.

In the Cluster Core Resources section, right-click cluster name and click Bring Online. Then, wait until both resources are online. When the cluster name resource comes online, it updates the DC server with a new AD computer account. Use this AD account to run the Availability Group clustered service later.

Please, see Enhanced Proactive Monitoring with Veeam ONE, and SQL Server 2025 Upgrade Requirements and Compatibility

Add the other SQL Server to cluster

Add the other SQL Server to the cluster. From the browser tree, right-click the cluster and click Add Node.

In the Add Node Wizard, click Next. On the Select Servers page, add the second SQL Server. Type the server name in Enter server name and then click Add. When you are done, click Next.

In the Validation Warning page, click No (in a production scenario you should perform the validation tests). Then, click Next.

In the Confirmation page if you are using Storage Spaces, clear the checkbox labeled Add all eligible storage to the cluster.

Warning If you are using Storage Spaces and do not uncheck Add all eligible storage to the cluster, Windows detaches the virtual disks during the clustering process. As a result, they do not appear in Disk Manager or Explorer until the storage spaces are removed from the cluster and reattached using PowerShell. Storage Spaces groups multiple disks in to storage pools. For more information, see the following link.

Click Next, and click Finish.

Next, the Failover Cluster Manager shows that your cluster has a new node and lists it in the Nodes container.

Log out of the remote desktop session.

Set up a Cloud Witness for a cluster

Here, you will have to create an Azure Storage Account to use as a Cloud Witness. This section describes how to create a storage account and view and copy endpoint URLs and access keys for that account.

To configure Cloud Witness, you must have a valid Azure Storage Account which can be used to store the blob file (used for arbitration). Cloud Witness creates a well-known Container msft-cloud-witness under

the Microsoft Storage Account. Cloud Witness writes a single blob file with corresponding cluster’s unique ID used as the file name of the blob file under this msft-cloud-witness container. This means that you can use the same Microsoft Azure Storage Account to configure a Cloud Witness for multiple different clusters.

When you use the same Azure Storage Account for configuring Cloud Witness for multiple different clusters, a single msft-cloud-witness container gets created automatically. This container will contain one-blob file per cluster.

To create an Azure storage account

Sign in to the https://portal.azure.com

1. On the Hub menu, select New, Data + Storage and Storage account.
2. In the Create a storage account page, do the following:
   1. Enter a name for your storage account.
      Storage account names must be between 3 and 24 characters in length and may contain numbers and lowercase letters only. The storage account name must also be unique within Azure.
   1. For Account kind, select General purpose.
      You can’t use a Blob storage account for a Cloud Witness.
   1. For Performance, select Standard.
      You can’t use Azure Premium Storage for a Cloud Witness.
3. For Replication, select Locally-redundant storage (LRS) .
   Failover Clustering uses the blob file as the arbitration point, which requires some consistency guarantees when reading the data. Therefore you must select Locally-redundant storage for Replication type.

View and copy storage access keys for your Azure Storage Account

When you create a Microsoft Azure Storage Account, it is associated with two Access Keys that are automatically generated – Primary Access key and Secondary Access key. For a first-time creation of Cloud Witness, use the Primary Access Key. There is no restriction regarding which key to use for Cloud Witness.

To view and copy storage access keys

In the Azure Portal, navigate to your storage account, click All settings and then click Access Keys to view, copy, and regenerate your account access keys. The Access Keys blade also includes pre-configured connection strings using your primary and secondary keys that you can copy to use in your applications (see figure 1).

View and copy endpoint URL Links

When you create a Storage Account, the following URLs are generated using the format:

https://<Storage Account Name>.<Storage Type>.<Endpoint>

Cloud Witness always uses Blob as the storage type. Azure uses .core.windows.net as the Endpoint. When configuring Cloud Witness, it is possible that you configure it with a different endpoint as per your scenario (for example the Microsoft Azure datacenter in China has a different endpoint).

Note: The endpoint URL is generated automatically by Cloud Witness resource and there is no extra step of configuration necessary for the URL.

To view and copy endpoint URL links

In the Azure Portal, navigate to your storage account, click All settings and then click Properties to view and copy your endpoint URLs (see figure 2). For more information about creating and managing Azure Storage Accounts, see the following link.

Configure Cloud Witness as a quorum witness for your cluster

Cloud Witness configuration is well-integrated within the existing Quorum Configuration Wizard built into the Failover Cluster Manager.

To configure Cloud Witness as a Quorum Witness

Launch Failover Cluster Manager. Right-click the cluster and select More Actions and then Configure Cluster Quorum Settings (see figure 3). This launches the Configure Cluster Quorum wizard.

On the Select Quorum Configurations page, select Select the quorum witness (see figure 4).

On the Select Quorum Witness page, select Configure a cloud witness (see figure 5).

On the Configure Cloud Witness page, enter the following information:

1. (Required parameter) Azure Storage Account Name.
2. (Required parameter) Access Key corresponding to the Storage Account.
   1. When creating for the first time, use Primary Access Key (see figure 5)
   2. When rotating the Primary Access Key, use Secondary Access Key (see figure 5)
3. (Optional parameter) If you intend to use a different Azure service endpoint (for example the Microsoft Azure service in China), then update the endpoint server name.

Configure your Cloud Witness

Upon successful configuration of Cloud Witness, you can view the newly created witness resource in the Failover Cluster Manager snap-in (see figure 6).

Enable Availability Groups

Next, enable the AlwaysOn Availability Groups feature. Do these steps on both SQL Servers.

1. From the Start screen, launch SQL Server Configuration Manager.
2. In the browser tree, click SQL Server Services, then right-click the SQL Server (MSSQLSERVER) service and click Properties.
3. Click the AlwaysOn High Availability tab, then select Enable AlwaysOn Availability Groups, as follows:

1. Click Apply. Click OK in the pop-up dialog.
2. Restart the SQL Server service.

Repeat these steps on the other SQL Server.

Please, see “Virtual Machine Scale Set: Demonstrating High Availability in Azure“, and “Failover Cluster Manager failed while managing one or more clusters, the error was unable to determine if the computer exists in the domain“.

Create a database on the first SQL Server

Launch the RDP file to the first SQL Server with a domain account that is a member of sysadmin fixed server role.

1. Open SQL Server Management Studio and connect to the first SQL Server.
2. In Object Explorer, right-click Databases and click New Database.
3. In Database name, type MyDB1, then click OK.

Next, create a backup share

1. On the first SQL Server in Server Manager, click Tools. Open Computer Management.
2. Click Shared Folders, and right-click Shares, and click New Share….

Use Create a Shared Folder Wizard to create a share.

1. On Folder Path, click Browse and locate or create a path for the database backup shared folder. Click Next.
2. In Name, Description, and Settings verify the share name and path. Click Next.
3. On Shared Folder Permissions set Customize permissions. Click Custom….
4. On Customize Permissions, click Add….
5. Make sure that the SQL Server and SQL Server Agent service accounts for both servers have full control.

6.Click OK. In Shared Folder Permissions, click Finish. Click Finish again.

Create the Availability Group

You are now ready to configure an Availability Group using the following steps:

• Create a database on the first SQL Server.
• Take both a full backup and a transaction log backup of the database
• Restore the full and log backups to the second SQL Server with the NORECOVERY option
• Create the Availability Group (AG1) with synchronous commit, automatic failover, and readable secondary replicas

Create the Availability Group: On remote desktop session to the first SQL Server. In Object Explorer in SSMS, right-click AlwaysOn High Availability and click New Availability Group Wizard.

2. In the Introduction page, click Next. In the Specify Availability Group Name page, type a name for the Availability Group, for example AG1, in Availability group name. Click Next.

Please, see How to add a second Domain Controller, and how to Install and configure Hyper-V Cluster On Windows Server.

Select database

3. In the Select Databases page, select your database and click Next.

Note: The database meets the prerequisites for an Availability Group because you have taken at least one full backup on the intended primary replica.

4. In the Specify Replicas page, click Add Replica.

5. The Connect to Server dialog pops up. Type the name of the second server in Server name. Click Connect.

Back in the Specify Replicas page, you should now see the second server listed in Availability Replicas. Configure the replicas as follows.

6. Click Endpoints to see the database mirroring endpoint for this Availability Group. Use the same port that you used when you set the firewall. Please, see for more information.

Initial Data Synchronization

7. In the Select Initial Data Synchronization page, select Full and specify a shared network location. For the location, use the example it was, \\<First SQL Server>\Backup\. Click Next. For more information, see the following link.

Note: Full synchronization takes a full backup of the database on the first instance of SQL Server and restores it to the second instance. For large databases, full synchronization is not recommended because it may take a long time.

You can reduce this time by manually taking a backup of the database and restoring it with NO RECOVERY. If the database is already restored with NO RECOVERY on the second SQL Server before configuring the Availability Group, choose Join only. If you want to take the backup after configuring the Availability Group, choose Skip initial data synchronization.

8. In the Validation page, click Next. This page should look similar to the following image:

Note There is a warning for the listener configuration because you have not configured an Availability Group listener. You can ignore this warning because on Azure virtual machines you create the listener after creating the Azure load balancer

In the Summary page, click Finish, then wait while the wizard configures the new Availability Group. In the Progress page, you can click More details to view the detailed progress. Once the wizard is finished, inspect the Results page to verify that the Availability Group is successfully created

10. Click Close to exit the wizard.

Check the Availability Group

In Object Explorer, expand AlwaysOn High Availability, then expand Availability Groups. You should now see the new Availability Group in this container. Right-click the Availability Group and click Show Dashboard.

Your AlwaysOn Dashboard should look similar to this.

You can see the replicas, the failover mode of each replica and the synchronization state.

In Failover Cluster Manager, click your cluster. Select Roles. The Availability Group name you used is a role on the cluster. That Availability Group does not have an IP address for client connections, because you did not configure a listener. You will configure the listener after you create an Azure load balancer.

Warning Do not try to fail over the Availability Group from the Failover Cluster Manager. All failover operations should be performed from within AlwaysOn Dashboard in SSMS. For more information, see the following link.

At this point, you have an Availability Group with replicas on two instances of SQL Server. You can move the Availability Group between instances. You cannot connect to the Availability Group yet because you do not have a listener. In Azure virtual machines, the listener requires a load balancer. The next step is to create the load balancer in Azure.

Create an Azure load balancer

On Azure virtual machines, a SQL Server Availability Group requires a load balancer. The load balancer holds the IP addresses for the Availability Group listeners and the Windows Server Failover Cluster. This section summarizes how to create the load balancer in the Azure portal.

1. In the Azure portal, go to the resource group where your SQL Servers are and click + Add.
2. Search for Load Balancer. Choose the load balancer published by Microsoft.

Load Balancer Parameters

3. Click Create, and next, you will have to configure following parameters for the load balancer.

Setting	Field
Name	Use a text name for the load balancer, for example sqlLB.
Type	Internal
Virtual network	Use the name of the Azure virtual network.
Subnet	Use the name of the subnet that the virtual machine is in.
SKU	Standard
IP address assignment	Static
Private IP address	Use an available address from subnet. Use this address for your availability group listener. Note that this is different from your cluster IP address.
Subscription	Use the same subscription as the virtual machine.
Location	Use the same location as the virtual machine.
Type	Standard
Zone	Zone-redundant

5. Click Create, to create the load balancer

To configure the load balancer, you need to create a backend pool, a probe, and set the load balancing rules. Do these in the Azure portal.

Add backend pool for the availability group listener

In the Azure portal, go to your availability group. You might need to refresh the view to see the newly created load balancer.

2. Click the load balancer, click Backend pools, and click +Add.
3. Type a name for the backend pool.
4. Associate the backend pool with the availability set that contains the VMs.

5. Under Target network IP configurations, check VIRTUAL MACHINE and choose both of the virtual machines that will host availability group replicas. Do not include the file share witness server.

Note: If both virtual machines are not specified, connections will only succeed to the primary replica.

6. Click OK to create the backend pool.

Set the Probe

This chapter of the procedure creates the Load Balancer rules relating to the ‘role’ listener, so if there are multiple instances under the role, it will be necessary to create multiple probes and multiple load balancing rules. It will be necessary to use a different IP address for each instance, but it is possible to use the same port on all of them (for example, 59999).

If this is not your first listener for this Load Balancer (the first one is created automatically), you will need to do the following:

• go to Frontend IP configuration, press add
• set the address you have decided to assign to the new listener as a static IP

1. Click the load balancer, click Health probes, and click +Add.
2. Set the listener health probe as follows:

Setting	Description	Example
Name	Text	Your_DB_Probe
Protocol	Choose TCP	TCP
Port	Any unused port	59999
Interval	The amount of time between probe attempts in seconds	5
Unhealthy threshold	The number of consecutive probe failures that must occur for a virtual machine to be considered unhealthy	2

3. Click OK to set the health probe

Set the load balancing rules

1. Click the load balancer, click Load balancing rules, and click +Add.
2. Set the listener load balancing rules as follows.

Setting	Description	Example
Name	Text	YOUR_INSTANCE_Listener
Frontend IP address	Choose an address	IP scelto per l’istanza del listener
Protocol	Choose TCP	TCP
Port	Use the port for the availability group listener	1433
Backend Port	This field is not used when Floating IP is set for direct server return (se vi segna errore abilitate prima il campo Floating IP a Enabled)	1433
Probe	The name you specified for the probe	YOUR_INSTANCE_Probe
Session Persistence	Drop down list	None
Idle Timeout	Minutes to keep a TCP connection open	4
Floating IP (direct server return)		Enabled

3. Warning direct server return is set during creation. It cannot be changed.
4. Click OK to set the listener load balancing rules.

Add the cluster core IP address for the Windows Server Failover Cluster (WSFC)

This section, on the other hand, relates to the configuration of the cluster IP, so it only needs to be done once.

In the portal, on the same Azure load balancer, click Frontend IP configuration and click +Add. Use the IP Address you configured for the WSFC in the cluster core resources.

Set the “Assignment” field as static.

2. On the load balancer, click Health probes, and click +Add.
3. Set the WSFC cluster core IP address health probe as follows:

Setting	Description	Example
Name	Text	Nomedel clusterProbe
Protocol	Choose TCP	TCP
Port	Any unused port	58888
Interval	The amount of time between probe attempts in seconds	5
Unhealthy threshold	The number of consecutive probe failures that must occur for a virtual machine to be considered unhealthy	2

4. Click OK to set the health probe.
5. Set the load balancing rules. Click Load balancing rules, and click +Add.

Cluster IP Load balancing Rule

6. Set the cluster core IP address load balancing rules as follows.

Setting	Description	Example
Name	Text	Nomedel clusterEndPoint
Frontend IP address	Choose an address	Use the address that you created when you configured the WSFC IP address. (ip del cluster)This is different from the listener IP address
Protocol	Choose TCP	TCP
Port	Use the port for the cluster IP address. This is an available port that is not used for the listener probe port.	58888
Backend Port	This field is not used when Floating IP is set for direct server return	58888
Probe	The name you specified for the probe	Nomedel clusterProbe
Session Persistence	Drop down list	None
Idle Timeout	Minutes to keep a TCP connection open	4
Floating IP (direct server return)		Enabled

7. Warning
8. Direct server return is set during creation. It cannot be changed.
9. Click OK to set the load balancing rules.

Configure the listener

A listener will be created for each instance in the cluster.

Note: This tutorial shows how to create a single listener – with one ILB IP address. To create one or more listeners using one or more IP addresses, see the following link for more information.

The availability group listener is an IP address and network name that the SQL Server availability group listens on. To create the availability group listener, do the following:

Add the client access point

1: The client access point is the network name that applications use to connect to the databases in an availability group. Create the client access point in Failover Cluster Manager.

• Expand the cluster name, and then click Roles.

• In the Roles pane, right-click the availability group name, and then select Add Resource > Client Access Point.

• In the Name box, create a name for this new listener. The name for the new listener is the network name that applications use to connect to databases in the SQL Server availability group.

• To finish creating the listener, click Next twice, and then click Finish. Do not bring the listener or resource online at this point.

2: Take the availability group cluster role offline. In Failover Cluster Manager under Roles, right-click the role, and select Stop Role.

3: Configure the IP resource for the availability group.

• Click the Resources tab, and then expand the client access point you created.

The client access point is offline.

b. Right-click the IP resource, and then click properties. Note the name of the IP address, and use it in the $IPResourceName variable in the PowerShell script.

Change the name of the IP resource according to your company's naming convention. <instance_name>_<addressip>  es: your_instance_name_Test_10.00.10.3

Set IP Address

c. Under IP Address, click Static IP Address. Set the IP address as the same address that you used when you set the load balancer address on the Azure portal.

4. Make the SQL Server availability group resource dependent on the client access point.

a. In Failover Cluster Manager, click Roles, and then click your availability group.

b. On the Resources tab, under Other Resources, right-click the availability resource group, and then click Properties.

c. On the dependencies tab, add the name of the client access point (the listener) resource. Next, click OK.

5. Make the client access point resource dependent on the IP address.

• In Failover Cluster Manager, click Roles, and then click your availability group.
• On the Resources tab, right-click the client access point resource under Server Name, and then click Properties.

(If an error occurs, try closing and reopening the failover cluster manager.)

I hope you found this article on the Procedure for creating an MSSQL Always On Cluster on Azure very useful. Please, feel free to leave a comment below.