The Always On configuration of SQL Server in Azure Virtual Machines differs in multiple ways from the on-premise implementations. These differences reflect some of the unique characteristics of the storage and network infrastructure services in the Microsoft cloud environment. This Microsoft article link will give you a detailed explanation about how Always On needs to be configured in Azure virtual machines that are running with Azure Resource Manager (ARM). This article gives you a brief explanation about the steps that needs to be done for this.

An Availability Group (AG) requires a load balancer when the SQL Server instances are running on Azure virtual machines. The load balancer will have the same IP address for the availability group listener. If an availability group spans multiple regions, each region needs a load balancer. Support from both the Azure\Cloud Team and SQL Server DBA Team are required for the successful implementation of Always On in Azure Servers.

Generally, in on-premises servers, the Always On configuration is simple enough that we can create it by right clicking the Add Listener option in SSMS. This will automatically create the cluster roles in the Failover Cluster Manager wizard. But in Azure servers, it is not the same way as it is in the on premise servers. This article will give you the steps to configure the Always On Listener in Azure servers.

Create and configure the load balancer

This link explains in detail about the 4 steps to configure the Load Balancer in the Azure Portal.

This often will be taking care by the Azure Team in an organization. The overview of the process is given here.

1. Create the load balancer and configure the IP address
2. Configure the back-end pool
3. Create a probe
4. Set the load balancing rules

Configure the cluster to use the load balancer IP address

This section gives you a quick summary of what needs to be done from a SQL Server DBA perspective once the Azure Team completed done the load balancer configuration.

1. Create an availability group in SQL Server from SQL Server Management Studio same as the way how you do for the on premise Virtual Machines. You can refer to the

   'Create the Availability Group’ Section in the Microsoft site if you need more details.

2. In the Failover Cluster Manager console, identify the name of the cluster network to which both nodes are connected and note it down which needs to be used as a variable $ClusterNetworkName in the powershell script in Step 8.
3. In the Failover Cluster Manager console, create a Client Access Point representing the listener.
4. In the Failover Cluster Manager console, identify the name of the IP Address resource of the newly created Client Access Point. This IP resource Name will be used as a variable $IPResourceName in the powershell script in Step 8.
5. In the Failover Cluster Manager console, set the Static IP Address property of the IP Address resource you identified in the previous step to the front-end IP address of the Azure load balancer. This will be the IP for your SQL Server Always On Listener.
6. In the Failover Cluster Manager console, create a dependency of the availability group role on the name resource of Client Access Point.
7. In the Failover Cluster Manager console, bring the Client Access Point resource online
8. use Windows PowerShell to configure cluster parameters by running the following script:

Below script will set the Cluster Network Name (from Step 2), IP Resource Name (from Step 4), Listener Load Balancer IP (SQL Server listenert IP) and Listener Probe Port (to be provided by Azure Team) by passing the respective parameters to the Set-ClusterParameter commandlet. This script needs to be executed on both nodes

$ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "<IPResourceName>" # the IP Address resource name
$ListenerILBIP = "<n.n.n.n>" # 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. This will be your SQL Server listener Name.
[int]$ListenerProbePort = <nnnnn> ## check with the Azure Team for the ILB Health probe port number. This is NOT SQL Listener port.
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

The below screenshot shows the above script in the Powershell ISE.

Configure the port number for the Listener

If the cluster resources and dependencies are configured correctly, you should be able to view the listener in SQL Server Management Studio. Once the listener name reflects in the SSMS console, set up the listener port that you wish the application to use to connect to SQL Server by following the below steps.

1. Start SQL Server Management Studio, and then connect to the primary replica.
2. Go to AlwaysOn High Availability > Availability Groups > Availability Group Listeners. You should now see the listener name that you created in Failover Cluster Manager.
3. Right-click the listener name, and then click Properties.
4. In the Port box, specify the port number for the availability group listener by using the $EndpointPort you used earlier (1433 was the default), and then click OK.

You now have an availability group in Azure virtual machine running in Resource Manager mode.

Reboot Required in case of the error 'Failed to bring resource online'

Once the above said configuration is done, a reboot of both the nodes are required to properly apply the Listener configuration settings across the nodes. If not, there is a chance of the Resource Offline Error as shown below :

The error that you may face while bringing up the Client Access Point resource online is shown in the screenshot. This looks odd as the error make us to dig into deep level troubleshooting steps. In fact it took me 2 days to understand that the reboot is the solution here to get this worked. I was initially hesitated to do the reboot as the matter of fact that the servers are in production, and moreover, I didn’t believe that the reboot can fix this. But, please reboot the server if you are getting this error post the Always On configuration in Azure Servers, which should fix this issue.

Hope this will help some one who is searching for a solution for this issue while configuring the Always On part.