Share via

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

  • Article

In a previous post, we have reviewed Azure RM: How to create SQL Server AlwaysOn Availability Groups Listener configuration with Azure External Load Balancer, 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

  1. Create a Public IP address for the Front-End IP pool
  2. Add to the Load Balancer the new Public IP
  3. Add to the Load Balancer a new Health Probe
  4. Add to the Load Balancer a new Load Balancing Rule
  5. 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:

  1. Create the availability group listener on the failover cluster
  2. Bring the listener online and configure the port number
  3. 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
11_RG_AG_Online

Verify that the Public IP is configured in the NIC
12_ValidateIP

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

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 14_AGs_Configs

3. Validate the access over the listener trough Internet

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

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

15_Access3

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

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

16_Access4

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

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!!!
Twitter @carlos_sfc

Comments

  • Anonymous
    February 12, 2019
    Thanks!