Azure RM: SQL Server AlwaysOn Availability Groups Listener configuration with Azure External Load Balancer

I want to thank to Ruben Gonzalez for his guidance.

This blog post explains how to configure SQL Server AlwaysOn AG with an External Listener in Azure VMs running in Resource Manager model.

Pre-requisites

Before to start ensures that the environment is ready, deploy VMs in Azure RM model:

• Create a Resource Group.
• Create a Virtual Network.
• Create an Availability Set. (Required)
• Create a Network Security Group. (Optional)
• Deploy Azure VMs:
  1. One VM for the Domain Controller with an Active Directory.
  2. Two SQL Server VMs deployed into the VN Subnet and joined to AD Domain.
  3. One VM to configure the File Share Witness Quorum Model.
  4. Two availability groups with two synchronous-commit replicas of an availability database.

Concepts

Internet Facing load balancer

Azure load balancer maps the public IP address and port number of incoming traffic to the private IP address and port number of the virtual machine and vice versa for the response traffic from the virtual machine.

Azure Load Balancer contains the following child resources:

• Front end IP configuration - contains public IP addresses for incoming network traffic.
• Back end address pool - contains network interfaces (NICs) for the virtual machines to receive network traffic from the load balancer.
• Load balancing rules - contains rules mapping a public port on the load balancer to port in the back end address pool.
• Inbound NAT rules - contains rules mapping a public port on the load balancer to a port for a specific virtual machine in the back end address pool.
• Probes - contains health probes used to check availability of virtual machines instances in the back end address pool.

Configure the external load balancer.

With the following steps you will create and configure an Internet Facing Load Balancer and then you will configure the cluster to use the Public IP address from the load balancer for the AlwaysOn availability group listener.

1. Setup PowerShell to use Resource Manager

 
# To login to Azure Resource Manager
Login-AzureRmAccount
# To view all subscriptions for your account
Get-AzureRmSubscription
# To select a subscription for your current session
Get-AzureRmSubscription –SubscriptionName "Subscription Name" | Select-AzureRmSubscription

2. Create a Public IP address for the Front-End IP pool

Create an Azure Public IP address (PIP) resource, named MSPublicIP01, to be used as front-end with DNS name msagapp01.centralus.cloudapp.azure.com. The command below uses the static allocation type.

 
#Create a virtual network and a public IP address for the front-end IP pool

$publicIP = New-AzureRmPublicIpAddress -Name "MSPublicIP01" -ResourceGroupName "MSRGAlwaysON" -Location 'Central US' –AllocationMethod Static -DomainNameLabel "msagapp01"

3. Create Load Balancer with Front-End IP pool and a Back-End Address Pool

The following script will create a Load Balancer with this child items:

 
##Create a Front-End IP pool and a Back-End Address Pool
#Front End IP
$frontendIP    =  New-AzureRmLoadBalancerFrontendIpConfig         -Name LB-MSFrontend -PublicIpAddress $publicIP 

#BackEnd Adress Pool 
$beaddresspool =  New-AzureRmLoadBalancerBackendAddressPoolConfig -Name LB-MSBackEnd

#Health Probe Port
$healthProbe   = New-AzureRmLoadBalancerProbeConfig -Name LB-MSHealthProbe -Protocol Tcp -Port 59999 -IntervalInSeconds 5 -ProbeCount 2

#Load Balancer Rule
#Important to note that for AlwaysOn Availability Group Listener the FrontEnd and BackEnd Port must be the same and EnableFloatingIP must be specified.
$lbrule        = New-AzureRmLoadBalancerRuleConfig  -Name LB-MSRuleSQLAG01 -FrontendIpConfiguration $frontendIP -BackendAddressPool  $beAddressPool -Probe $healthProbe -Protocol Tcp -FrontendPort 2550 -BackendPort 2550 -EnableFloatingIP -LoadDistribution SourceIPProtocol

#Create the Azure Load Balancer with the above configurations
$NRPLB         = New-AzureRmLoadBalancer -ResourceGroupName MSRGAlwaysON -Name MSLB -Location 'Central US' -FrontendIpConfiguration $frontendIP -InboundNatRule $inboundNATRule1 -LoadBalancingRule $lbrule -BackendAddressPool $beAddressPool -Probe $healthProbe

Note: In order to minimize complexity, in the Load Balancer Rule the FrontendPort and the BackendPort are the same, but it should work even using different ports, the recommendation always is test, test and test.

4. Join the VMs NICs to the Backed Pool in the Load Balancer

Azure calls the backend address pool backend pool. In this case, the backend pool is the addresses of the two SQL Servers in your availability group.

 
#Join the VMs NICs to the Backed Pool in the Load Balancer
#Get NIC Name of VM1
$VM1= Get-AzureRmVM -ResourceGroupName MSRGAlwaysON -Name MSSQL01
$nic1Name=$VM1.NetworkProfile.NetworkInterfaces[0].Id
$nic1Name= $nic1Name.Substring(($nic1Name.LastIndexOf("/")+1) , $nic1Name.Length-($nic1Name.LastIndexOf("/")+1))
$nic1 = Get-AzureRmNetworkInterface -ResourceGroupName MSRGAlwaysON -Name $nic1Name

#Get NIC Name of VM2
$VM2= Get-AzureRmVM -ResourceGroupName MSRGAlwaysON -Name MSSQL02
$nic2Name=$VM2.NetworkProfile.NetworkInterfaces[0].Id
$nic2Name= $nic2Name.Substring($nic2Name.LastIndexOf("/")+1 , $nic2Name.Length-($nic2Name.LastIndexOf("/")+1))
$nic2 = Get-AzureRmNetworkInterface -ResourceGroupName MSRGAlwaysON -Name $nic2Name

# Join NICs to LB Backend Pools
$nic1.IpConfigurations[0].LoadBalancerBackendAddressPools.Add($NRPLB.BackendAddressPools[0]);
$nic2.IpConfigurations[0].LoadBalancerBackendAddressPools.Add($NRPLB.BackendAddressPools[0]);

$nic1 | Set-AzureRmNetworkInterface
$nic2 | Set-AzureRmNetworkInterface

5. Configure the Network Security Group Inbound Rules

Network security group (NSG) contains a list of Access Control List (ACL) rules that allow or deny network traffic to your VM instances in a Virtual Network. NSGs can be associated with either subnets or individual VM instances within that subnet. When a NSG is associated with a subnet, the ACL rules apply to all the VM instances in that subnet. In addition, traffic to an individual VM can be restricted further by associating a NSG directly to that VM.

 
# Configure the Network Security Group to Allow access over ports 1433 SQLSVC and 2550 SQL AG Listener
$nsg = Get-AzureRmNetworkSecurityGroup -ResourceGroupName MSRGAlwaysON -Name MSSG

$nsgrule1=Add-AzureRmNetworkSecurityRuleConfig -NetworkSecurityGroup $nsg -Name sqlsvc -Description "Allow port 1433" -Access Allow -Protocol Tcp -Direction Inbound -Priority 1010 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 1433

$nsgrule2=Add-AzureRmNetworkSecurityRuleConfig -NetworkSecurityGroup $nsg -Name sqlag01 -Description "Allow port 2550" -Access Allow -Protocol Tcp -Direction Inbound -Priority 1020 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 2550

Set-AzureRmNetworkSecurityGroup -NetworkSecurityGroup $nsg

Note: If you have a Network Security Group associated per single VM then you have to execute the above script for every NSG.

6. Configure the cluster to use the load balancer IP address

The next step is to configure the listener on the cluster, and bring the listener online. To accomplish this, do the following:

1. Create the availability group listener on the failover cluster
2. Bring the listener online and configure the port number
3. Open Firewall Ports

Create the availability group listener on the failover cluster
Go to the Failover Cluster Manager>Roles>SQLApp1 (AlwaysOn Availability Group)
On the Actions Pane click on Add Resource and then Client Access Point

Add the Name > This will be the name of the Listener

Next on the Confirmation Page

Next on the Summary Page

On the Resource Group right click on the IP Resource Address and the properties, set the resource name to IPListner1

On the cluster node that currently hosts the primary replica, open an elevated PowerShell ISE and paste the following commands into a new script.

 
# the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$ClusterNetworkName = "SQLPublic"

# the IP Address resource name
$IPResourceName = "IPListener1"

# The IP Address of the Internal Load Balancer (ILB).
# This is the static IP address for the load balancer you configured in the Azure RM.
$ILBIP = "40.83.10.48" #MSPublicIP01

Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

On the Cluster Resource SQLApp1 right click and then go offline

Then go to the resource properties>dependencies tab and Add the mssqlapp1 resource as dependency.

Bring the listener online and configure the port number
Bring the resource SQLApp1 Online

Verify that the Public IP is configured in the NIC

Configure the listener port
Open SSMS and the go to Availability Groups>SQLApp1>Listener>mssqlapp1 right click and properties

Open Firewall Ports
Open a CMD and create firewall rules to allow connections over ports 1433 (SQLSVC), 2550 (SQL AG Listener), 59999 (Probe Port)

 
netsh firewall add portopening TCP 1433 "Open Port 1433"
netsh firewall add portopening TCP 2550 "Open Port 2550"
netsh firewall add portopening TCP 59999 "Open Port 59999"

7. Validate the access over the listener inside the VMs and trough Internet

In the VM1

[code]sqlcmd -S MSSQLAPP1,2550 -E -dAPP1 -Q"SELECT @@SERVERNAME"

In the VM2

[code]sqlcmd -S MSSQLAPP1,2550 -E -dAPP1 -Q"SELECT @@SERVERNAME"

Over the Internet
Use the DNS name configured in the Public IP

[code]sqlcmd -S msagapp01.centralus.cloudapp.azure.com,2550 -Usqladmin -dAPP1 -Q"SELECT @@SERVERNAME"

The objective is test the access trough the listener inside the VM's and trough the External Load Balancer that uses the Public IP with the DNS name and port 2550, both tests were a success!

8. Configure the ReadOnly routing list

Open SSMS connect to the Primary Replica > Open a new query window

 
--SPECIFY TO ACCEPT READ-ONLY CONNECTIONS
ALTER AVAILABILITY GROUP SQLApp1
MODIFY REPLICA ON N'MSSQL01' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

ALTER AVAILABILITY GROUP SQLApp2
MODIFY REPLICA ON N'MSSQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

--SPECIFY A READ_ONLY_ROUTING_URL
ALTER AVAILABILITY GROUP SQLApp1
MODIFY REPLICA ON 'MSSQL01'WITH( SECONDARY_ROLE (READ_ONLY_ROUTING_URL='tcp://mssql01.centralus.cloudapp.azure.com:1433'))

ALTER AVAILABILITY GROUP SQLApp1
MODIFY REPLICA ON 'MSSQL02'WITH( SECONDARY_ROLE (READ_ONLY_ROUTING_URL='tcp://mssql02.centralus.cloudapp.azure.com:1433'))

--SPECIFY A READ-ONLY ROUTING LIST
ALTER AVAILABILITY GROUP SQLApp1
MODIFY REPLICA ON 'MSSQL01'WITH( PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =('MSSQL02','MSSQL01')))

ALTER AVAILABILITY GROUP SQLApp1
MODIFY REPLICA ON 'MSSQL02'WITH( PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =('MSSQL01','MSSQL02')))

9. Validate the access over the listener inside the VMs and trough Internet

In the VM1

[code]sqlcmd -S MSSQLAPP1,2550 -Usqladmin -dAPP1 -Q"SELECT @@SERVERNAME" -KREADONLY

In the VM2

[code]sqlcmd -S MSSQLAPP1,2550 -Usqladmin -dAPP1 -Q"SELECT @@SERVERNAME" -KREADONLY

Over the Internet
Use the DNS name configured in the Public IP

[code]sqlcmd -S msagapp01.centralus.cloudapp.azure.com,2550 -Usqladmin -dAPP1 -Q"SELECT @@SERVERNAME" -KREADONLY

The objective is test the access trough the listener with the option ReadOnly in order to route the connection to the Secondary Replica, this test were executed inside the VM's and trough the External Load Balancer that uses the Public IP with the DNS name and port 2550, both tests were a success!

References

Azure Resource Manager Support for Load Balancer
From < https://azure.microsoft.com/en-us/documentation/articles/load-balancer-arm/ >
 
Get started creating an Internet facing load balancer in Resource Manager using PowerShell
From < https://azure.microsoft.com/en-us/documentation/articles/load-balancer-get-started-internet-arm-ps/ >
 
Internet Facing load balancer between multiple Virtual Machines or services
From < https://azure.microsoft.com/en-us/documentation/articles/load-balancer-internet-overview/ >
 
Multi VIP Load balancer in ARM
From < https://blogs.technet.microsoft.com/espoon/2016/03/11/multi-vip-load-balancer-in-arm/ >
 
What is a Network Security Group (NSG)?
From < https://azure.microsoft.com/en-us/documentation/articles/virtual-networks-nsg/ >
 
High availability and disaster recovery for SQL Server in Azure Virtual Machines
From < https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-high-availability-dr/ >
 
Configure Always On availability group in Azure VM manually - Resource Manager
From < https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-portal-sql-alwayson-availability-groups-manual/ >
 
Configure an internal load balancer for an AlwaysOn availability group in Azure
From < https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-portal-sql-alwayson-int-listener/ >
 
Azure ARM: SQL Server High-Availability and Multi-Datacenter Disaster Recovery with Internal Load Balancers (ILB)
From < https://blogs.msdn.microsoft.com/igorpag/2016/01/26/azure-arm-sql-server-high-availability-and-multi-datacenter-disaster-recovery-with-internal-load-balancers-ilb/ >
 
 
If you still reading this very large post I want to say thank you!!!, in a second part we are going to configure an additional Availability Group with a Listener configured with the same External Load Balancer but over a different Public IP / Port Number.
Twitter @carlos_sfc