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.

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. However, in Azure servers, it is not the same way as it is in the on-premise servers.

In this article, we will discuss the steps to configure the Always On Listener in Azure servers.

Create and Configure the Load Balancer

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.

The Azure Team in an organization is responsible for creating and configuring the load balancer. The process involves the following steps:

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

Once the Azure Team has completed the load balancer configuration, the SQL Server DBA needs to configure the cluster to use the load balancer IP address.

Configure the Cluster to Use the Load Balancer IP Address

From a SQL Server DBA perspective, the following steps need to be done:

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.
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 provided script.

Once the cluster resources and dependencies are configured correctly, you should be able to view the listener in SQL Server Management Studio. Set up the listener port that you wish the application to use to connect to SQL Server.

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 is required to properly apply the Listener configuration settings across the nodes. If not, there is a chance of the Resource Offline Error.

If you encounter the error ‘Failed to bring resource online’ while bringing up the Client Access Point resource online, a reboot of the server is the solution to fix this issue.

Hope this article helps someone who is searching for a solution for configuring the Always On part in SQL Server on Azure Virtual Machines.