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

ItemProtocolPortDescription
Probe PortTCP59999SQL Server cluster probe to check the node that is currently hosting the Instance / Availability group (changes for each instance)

Load Balancing Rule (Availability Group)

ItemProtocolPortBackend portFloating IPDescription
SQL Server PortTCP15001500EnabledConnection to the SQL Server Availability Group Listener

Load Balancing Rule (Cluster)

ItemProtocolPortBackend portFloating IPDescription
SQL Server PortTCP14331433EnabledConnection to the SQL Server Clustered Instance (different port for named instances)

Load Balancing Rule (RPC)

ItemProtocolPortBackend portFloating IPDescription
RPC PortTCP135135EnabledConnection 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)

ItemProtocolPortBackend portFloating IPDescription
RPC PortTCP13551433DisabledConnection to the Host Server

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

ItemProtocolPortBackend portFloating IPDescription
RPC PortTCP13551500DisabledConnection 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

About the author