Multiple instances of SQL Server with Azure ILB

With the announcements at Ignite 2016 came support for multiple Virtual IP addresses on an Azure Internal Load Balancer (ILB).

This was quoted as

“Another use case is SQL Always On with Multiple Listeners which is now available in Preview. You can also host multiple availability groups on the same cluster and optimize the number of active replicas. “

In effect this means that multiple availability groups can share the same ILB

There are however limitations:

  • Only one Load balancer rule can be created for each Port and Back end server combinations
  • Only one Load balancer rule can be created for each Port and Front End IP Combination

So, for each SQL Server Instance to be able to function correctly behind the ILB it requires the following load balancer rules

Health Probes

Item Protocol Port Description
Probe Port TCP 59999 SQL Server cluster probe to check the node that is currently hosting the Instance / Availability group (changes for each instance)

Load Balancing Rule (Availability Group)

Item Protocol Port Backend port Floating IP Description
SQL Server Port TCP 1500 1500 Enabled Connection to the SQL Server Availability Group Listener

Load Balancing Rule (Cluster)

Item Protocol Port Backend port Floating IP Description
SQL Server Port TCP 1433 1433 Enabled Connection to the SQL Server Clustered Instance (different port for named instances)

Load Balancing Rule (RPC)

Item Protocol Port Backend port Floating IP Description
RPC Port TCP 135 135 Enabled Connection to the Host Server

Failure to add port 135 will result in the SQL Server management studio being unable to display the status of the SQL Server Instance or restart the service.

However, in the limitations we stated:

“Only one Load balancer rule can be created for each Port and Back end server combinations”

This means that we cannot have multiple rules for port 135 as the same backend servers are used for all the instances.

The way around this is to route the RPC via a different port to the backend server. This is achieved by configuring the following load balancer rules.

Load Balancing Rule (RPC) Instance 1 (Port 1433)

Item Protocol Port Backend port Floating IP Description
RPC Port TCP 135 51433 Disabled Connection to the Host Server

Load Balancing Rule (RPC) Instance 2 (Port 1500)

Item Protocol Port Backend port Floating IP Description
RPC Port TCP 135 51500 Disabled Connection to the Host Server

This routes the RPC traffic to ports 51433 and 51500 which do not handle RPC traffic on the host server.

To reroute the traffic to port 135 a netsh portproxy is required. This can be configured as follows

PortProxy Instance 1

netsh interface portproxy add v4tov4 listenport=51433 listenaddress=192.168.0.10 connectport=135 connectaddress=192.168.0.10

PortProxy Instance 2

netsh interface portproxy add v4tov4 listenport=51500 listenaddress=192.168.0.10 connectport=135 connectaddress=192.168.0.10

this will redirect the RPC traffic to the correct port and allow management studio to show the correct status for the SQL Server Instance

Post to Twitter

Leave a Comment

Your email address will not be published. Required fields are marked *