Core Infrastructure and Security Blog

9 MIN READ

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

Microsoft

Mar 15, 2019

First published on MSDN on Aug 08, 2016
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:

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:
Front end IP configurationBack end address poolProbesLoad balancing rules

Front IP in the LB will be the resource MSPublicIP01

This is only the child object that contains the NICs for the virtual machines to receive network traffic from the load balancer.

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

First probe will be on port 59999 and this will be validated every 5 sec.

The probe defines how Azure will verify which of the SQL Servers currently owns the availability group listener. Azure will probe the service based on IP address on a port that you define when you create the probe.

Maps public port 2550 on the load balancer to port 2550 in the back end address pool.

The load balancing rules configure how the load balancer routes traffic to the SQL Servers. For this load balancer you will enable direct server return because only one of the two SQL Servers will ever own the availability group listener resource at a time.c

##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:

Create the availability group listener on the failover cluster
Bring the listener online and configure the port number
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 sqlcmd -S MSSQLAPP1,2550 -E -dAPP1 -Q"SELECT @@SERVERNAME"

In the VM2

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

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

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

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

In the VM2

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

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

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!