Add additional IP addresses to a load balancer for more than one availability group

Add additional IP addresses to a load balancer for more than one availability group and Listener.

Here Customer has a requirements to add one more Listener  for a new Availability group in AlwaysOn in Azure IaaS.

Here assuming you have already configured AlwaysOn on 2 node cluster and you already have one listener and you are going to create a new listener in existing AlwaysOn setup.

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-prereq


lets start

 open a powershell module on your laptop/ server.


Login-AzureRmAccount

$ResourceGroupName = "my-centralusrg"          # My existing  Resource group name
$VNetName = "my-centralusrg-vnet"                  # My Existing Virtual network name
$SubnetName = "default"                        # My default Subnet name
$ILBName = "sqlLB"                          # My default ILB name                   

$ILBIP = "10.0.0.15"                  # IP address 10.0.0.15 is my new second IP address which will be #used
[int]$ListenerPort = "1450"                   # AG listener port as 1433 already used for previous Alistener
[int]$ProbePort = "57777"                     # Probe port  as 59999 is already in use hence giving 57777

$ILB = Get-AzureRmLoadBalancer -Name $ILBName -ResourceGroupName $ResourceGroupName

$count = $ILB.FrontendIpConfigurations.Count+1
$FrontEndConfigurationName ="FE_SQLAGILB_$count"

$LBProbeName = "ILBPROBE_$count"
$LBConfigrulename = "ILBCR_$count"

$VNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName
$Subnet = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $VNet -Name $SubnetName

$ILB | Add-AzureRmLoadBalancerFrontendIpConfig -Name $FrontEndConfigurationName -PrivateIpAddress $ILBIP -SubnetId $Subnet.Id

$ILB | Add-AzureRmLoadBalancerProbeConfig -Name $LBProbeName  -Protocol Tcp -Port $Probeport -ProbeCount 2 -IntervalInSeconds 15  | Set-AzureRmLoadBalancer

$ILB = Get-AzureRmLoadBalancer -Name $ILBname -ResourceGroupName $ResourceGroupName

$FEConfig = get-AzureRMLoadBalancerFrontendIpConfig -Name $FrontEndConfigurationName -LoadBalancer $ILB

$SQLHealthProbe  = Get-AzureRmLoadBalancerProbeConfig -Name $LBProbeName -LoadBalancer $ILB

$BEConfig = Get-AzureRmLoadBalancerBackendAddressPoolConfig -Name $ILB.BackendAddressPools[0].Name -LoadBalancer $ILB

$ILB | Add-AzureRmLoadBalancerRuleConfig -Name $LBConfigRuleName -FrontendIpConfiguration $FEConfig  -BackendAddressPool $BEConfig -Probe $SQLHealthProbe -Protocol tcp -FrontendPort  $ListenerPort -BackendPort $ListenerPort -LoadDistribution Default -EnableFloatingIP | Set-AzureRmLoadBalancer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Go to First Node of Cluster

Go to Windows Firewall and add 57777 & 1450 in inbound rule
Go to 2nd  Node of Cluster

Go to Windows Firewall and add 57777 & 1450 in inbound rule

Go to SQl Server primary replica for 2nd AG and execute this command:-

USE [master]

GO

ALTER AVAILABILITY GROUP [AG2]

ADD LISTENER N'AG2' (

WITH IP

((N'10.0.0.15', N'255.255.255.0')

)

, PORT=1450);

GO

In windows Cluster you will find  screenshot like below..

if you will right click on the IP Address:10.0.0.15 you will find below screenshot

note the Name

The Name is AG2_10.0.0.15

Now execute the below powershell command in any of the node of cluster.

Change the variables as per your settings. 

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

$IPResourceName = "AG2_10.0.0.15" #The IP Address resource name

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

[int]$ProbePort = 57777

Import-Module FailoverClusters

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

You are done..

Now Test.... Test.... Test

--------------------------------------

You can test whether 2nd listener working or not by using below command

from First node of cluster and 2nd node of cluster

repeat the same from 2nd node of cluster

In this way we have a evidence that 2nd listener working properly..

Thanks for reading and please let me know if this blog helped you in achieving the task.

~~~~~~~~~~~~~~~~For Multi Subnet AlwaysOn Cluster in Azure ~~~~~~~~~~~~~~~~~~~~~

In Case if your SQL Subnet is spread across 2 Subnet, it means Multisubnet AlwaysOn in Azure
Here I am creating a APP_listener, which is spread across 2 subnet.
You Need to create a one Frontend Ip for  each IP address of listener.
Here  10.0.1.11 and 10.0.2.16 is listener IP addresses..

see the screenshot..

ensure you add LBConfigRuleName unique, while Configure multisubnet AlwaysOn Listener in Azure

For each FrontEnd IP address, you have to configure LB  rule too.

Login-AzureRmAccount
$ResourceGroupName = "XXXXforpoc"          # My existing  Resource group name
$VNetName = "platvnetdepgtqh"                  # My Existing Virtual network name
$SubnetName = "sqldrsubnet"                        # My default Subnet name
$ILBName = "sqlLoadBalancer"                          # My default ILB name                 
$ILBIP = "10.0.2.16"                  # IP address 10.0.0.15 is my new second IP address which will be #used
[int]$ListenerPort = "1452"                   # AG listener port as 1433 already used for previous Alistener
[int]$ProbePort = "52222"                     # Probe port  as 59999 is already in use hence giving 52222
$ILB = Get-AzureRmLoadBalancer -Name $ILBName -ResourceGroupName $ResourceGroupName
$count = $ILB.FrontendIpConfigurations.Count+1
$FrontEndConfigurationName ="FE_SQLAGILB_$count"
$LBProbeName = "ILBPROBE_$count"
$LBConfigrulename = "ILBCRDR_$count"
$VNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName
$Subnet = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $VNet -Name $SubnetName
$ILB | Add-AzureRmLoadBalancerFrontendIpConfig -Name $FrontEndConfigurationName -PrivateIpAddress $ILBIP -SubnetId $Subnet.Id
$ILB | Add-AzureRmLoadBalancerProbeConfig -Name $LBProbeName  -Protocol Tcp -Port $Probeport -ProbeCount 2 -IntervalInSeconds 15  | Set-AzureRmLoadBalancer
$ILB = Get-AzureRmLoadBalancer -Name $ILBname -ResourceGroupName $ResourceGroupName
$FEConfig = get-AzureRMLoadBalancerFrontendIpConfig -Name $FrontEndConfigurationName -LoadBalancer $ILB
$SQLHealthProbe  = Get-AzureRmLoadBalancerProbeConfig -Name $LBProbeName -LoadBalancer $ILB
$BEConfig = Get-AzureRmLoadBalancerBackendAddressPoolConfig -Name $ILB.BackendAddressPools[0].Name -LoadBalancer $ILB
$ILB | Add-AzureRmLoadBalancerRuleConfig -Name $LBConfigRuleName -FrontendIpConfiguration $FEConfig  -BackendAddressPool $BEConfig -Probe $SQLHealthProbe -Protocol tcp -FrontendPort  $ListenerPort -BackendPort $ListenerPort -LoadDistribution Default -EnableFloatingIP | Set-AzureRmLoadBalancer


Ensure port 1452 and 52222 are open on each VMs of Cluster

  -- Then Create a Listener---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

USE [master]

GO

ALTER AVAILABILITY GROUP [APPAG2]

ADD LISTENER N'APP_Listener' (

WITH IP

((N'10.0.1.11', N'255.255.255.192'),
(N'10.0.2.16', N'255.255.255.0')

)

, PORT=1452);

GO

Then execute below script in any one node of cluster, so that things get set properly..

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

$IPResourceName = "AG_App_10.0.2.16" #The IP Address resource name

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

[int]$ProbePort = 52222
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"=$ProbePort;"SubnetMask"="255.255.255.0";"Network"="$ClusterNetworkName";"EnableDhcp"=0}