Azure RM: Configure a second Availability Group with a Listener using the External Load Balancer

Carlos Lopez · ‎Mar 15 2019

First published on MSDN on Jan 31, 2017
In a previous post, we have reviewed Azure RM: How to create SQL Server AlwaysOn Availability Groups Listener configuration with Azure E... , this is the second part of the delivery, have fun.

In Azure Resource Manager a Load balancer can include one or more front end Public IP addresses, otherwise known as a virtual IPs (VIPs). These IP addresses serve as ingress for the traffic. Because of this we can have multiple Availability Groups in the same Replica Server each one with his own Listener, to do this we need to follow the next steps:
1. Configure the current Load Balancer to include a second Public IP

Create a Public IP address for the Front-End IP pool
Add to the Load Balancer the new Public IP
Add to the Load Balancer a new Health Probe
Add to the Load Balancer a new Load Balancing Rule
Add to the Network Security Group a new Inbound Security Rule

#Create a second virtual network and a public IP address for the front-end IP pool
 
$publicIP = New-AzureRmPublicIpAddress -Name "MSPublicIP02" -ResourceGroupName "MSRGAlwaysON" -Location 'Central US' –AllocationMethod Static -DomainNameLabel "msagapp02"
 
##Create a Front-End IP pool and a Back-End Address Pool
#Front End IP
$frontendIP    =  New-AzureRmLoadBalancerFrontendIpConfig         -Name LB-MSFrontend2 -PublicIpAddress $publicIP
 
#Health Probe Port
$healthProbe   = New-AzureRmLoadBalancerProbeConfig -Name LB-MSHealthProbe2 -Protocol Tcp -Port 59998 -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-MSRuleSQLAG02 -FrontendIpConfiguration $frontendIP -BackendAddressPool  $beAddressPool -Probe $healthProbe -Protocol Tcp -FrontendPort 2551 -BackendPort 2551 -EnableFloatingIP -LoadDistribution SourceIPProtocol
 
 
# Add the IP to the LB
#Updating with the configuration from Azure
 $lb=Get-AzureRmLoadBalancer -Name MSLB -ResourceGroupName MSRGAlwaysON
 
#Adding FE Configurations ot the LB
 $lb.FrontendIpConfigurations.Add($frontendIP)
 
 $lb.Probes.Add($healthProbe)
 $lb.LoadBalancingRules.Add($lbrule)
 $Set=Set-AzureRmLoadBalancer -LoadBalancer $lb
 
 # Configure the Network Security Group to Allow access over and 2550 SQL AG Listener. Rule for port 1433 SQLSVC was previously stablished. 
 
$nsg = Get-AzureRmNetworkSecurityGroup -ResourceGroupName MSRGAlwaysON
 
$nsgrule3=Add-AzureRmNetworkSecurityRuleConfig -NetworkSecurityGroup $nsg -Name sqlag02 -Description "Allow port 2551" -Access Allow -Protocol Tcp -Direction Inbound -Priority 1030 -SourceAddressPrefix * -SourcePortRange * -DestinationAddressPrefix * -DestinationPortRange 2551
 
Set-AzureRmNetworkSecurityGroup -NetworkSecurityGroup $nsg

2. Configure the Cluster Resource to Add the Listener
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

In the previous post review the step 6 for more reference.

Create a new Client Access Point in the with the following parameters:
Cluster Resource: SQLApp2
Client Access Point Name: mssqlapp2
Cluster IP Resource: IPListener2

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 = "IPListener2"
 
# 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.86.84.150”  #MSPublicIP02
 
Import-Module FailoverClusters
 
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59998";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

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

Verify that the Public IP is configured in the NIC

Configure the listener port
Open SSMS and the go to Availability Groups>SQLApp2>Listner>mssqlapp2 right click and properties

Open Firewall Ports
Open a CMD and create firewall rules to allow connections over ports 2551 (SQL AG Listener), 59998 (Probe Port)

netsh firewall add portopening TCP 2551 "Open Port 2551"
netsh firewall add portopening TCP 59998 "Open Port 59998"

This is the final picture: two AGs with their own listener accepting connections from an external load balancer

3. Validate the access over the listener trough Internet
Over the Internet
Use the DNS name configured in the Public IP

sqlcmd -S msagapp02.centralus.cloudapp.azure.com,2551 -Usqladmin -dAPP2 -Q"SELECT @@SERVERNAME"

4. 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 SQLApp2 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 SQLApp2 MODIFY REPLICA ON 'MSSQL01'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = 'tcp://mssql01.centralus.cloudapp.azure.com:1433'))

ALTER AVAILABILITY
GROUP SQLApp2 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 SQLApp2 MODIFY REPLICA ON 'MSSQL01'
WITH (
		PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
				'MSSQL02'
				,'MSSQL01'
				))
		)

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

5. Validate the access over the listener trough Internet
Over the Internet
Use the DNS name configured in the Public IP

sqlcmd -S msagapp02.centralus.cloudapp.azure.com,2551 -Usqladmin -dAPP2 -Q"SELECT @@SERVERNAME" -KREADONLY

As final step failover the Availability Groups over the replicas and test the connectivity.

References

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

Azure RM: Configure a second Availability Group with a Listener using the External Load Balancer

References