SQL Always on Availability Groups – on Azure (Things you might oversee)

  • Article

There are tons of good documentation out there for SQL Always on AGs on Azure, here are some of the most comprehensive ones:

· SQL ALWAYS ON GUI - https://msdn.microsoft.com/en-us/library/azure/dn249504.aspx

· SQL ALWAYS ON POWERSHELL - https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-sql-server-alwayson-availability-groups-powershell/?rnd=1

 

I would highly recommend you go through both of these articles in both the articles above, as good as they are, there are still some things which you might oversee ( at least I did J ), especially in a secured environment.

 

Network Security Groups

 

How has NSGs got to do anything with configuring SQL Always on, you ask? Well, some organizations block out all WAN (INTERNET) traffic through a NSG rule (both inbound and outbound). So why should this affect your ILB configuration? The key word – “AZURE_LOADBALANCER” represents a datacenter IP in Azure which is used to monitor probes from the load balancer to determine the health state of virtual machines in a load balanced set.  Therefore, blocking this out would prevent the ILB from working and hence affect the working of the SQL Always on AG. If you have to deny “INTERNET” traffic through a NSG rule, please ensure you add another rule to the Network Security Group which has an allow from the “AZURE_LOADBALANCER” with a higher priority. Will write another detailed blog on this – on the working of NSGs and ILBs

 

Cluster Object

 

Ensure that the user performing the configuration on the SQL servers for Always on has permissions to create the Cluster Object on the domain (or) have an object pre-created.

 

Cluster Probe Script

 

If you are running Windows Server 2012 – ensure you run this below script :

 

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

$IPResourceName = "<IPResourceName>" # the IP Address resource name

$ILBIP = “<X.X.X.X>” # the IP Address of the Internal Load Balancer (ILB)

 

Import-Module FailoverClusters

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

 

 

If you are running Windows Server 2008 – run this :

 

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

$IPResourceName = "<IPResourceName>" # the IP Address resource name

$ILBIP = “<X.X.X.X>” # the IP Address of the Internal Load Balancer (ILB)

 

Import-Module FailoverClusters

cluster res $IPResourceName /priv enabledhcp=0 overrideaddressmatch=1 address=$ILBIP probeport=59999 subnetmask=255.255.255.255

 

 

IMPORTANT : BRING THE IP ADDRESS – offline and then bring it back online once you run this script. The probe port configuration is only complete if you bring it offline and then back online.

 

Once again, this might not be the best post for Azure SQL AG configuration and there could be tons of other things to look at, but these are my learnings :)