Multiple SQL FCI in Azure VMs

Question

Hi folks.
I've set up 2 VMs in Azure and deployed a WSFC on these servers.
I've installed one FCI and added a DNN because VNN doesn't work in Azure.
I've set a static port (tcp/1433) for the SQL service as I don't want to open up high ports in the firewall.
Then I've created another FCI in the same way as the first FCI was deployed.
This FCI is locked on port tcp/1434.
So here is the issue:
The DNN is registered in DNS which means that when applications are connecting to the listener it will randomly, DNS RR, get either SQL server 1 IP or SQL server 2 IP.
If I use SMSS to connect to app1listener it connects to the app1 FCI.
If I use SMSS to connect to app2listener it connects to the app1 FCI as well, because it defaults to tcp/1433 so I will have to specify the server port in my connection properties/string and since the DNN DNS names are pointing to the same SQL hosts it doesn't matter if you specify app1listener or app2listener as the target.

Am I missing something or is it possible to let internal FCI logic handle connections to the right instances based on which listener being accessed?

Thanks.

Answer 1

I've managed to get this working quite well by doing the following:
Create an Azure Load Balancer
Define two front end IP configurations, one for each instance.
Define one backend pool by selecting both of the two cluster nodes.
Define health probes for each instance, using high ports
Define two load balancing rules, one for each instance using the hard coded SQL server ports (TCP/1433 and TCP/1434)
As I can't define the same port more than once using the same backend pool, regardless of which front end IP I'm using, I can't set up rules for UDP/1434 towards both SQL servers.

On the cluster I ran the following PowerShell cmdlets:

 $ClusterNetworkName = "Cluster Network"
 $IPResourceName = "SQL IP Address 1 (<InstanceName>)" 
 $ILBIP = "10.0.4.202" #ALB Front end IP address
 [int]$ProbePort = 59991

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


 Get-ClusterResource "<ListenerName>" | Set-ClusterParameter RegisterAllProvidersIP 0  
 Get-ClusterResource "<ListenerName>" | Set-ClusterParameter HostRecordTTL 300 

This was done for both instances. Failover works as expected.

Answer 2

@Anders Jensen Apologies for the delay in response and all the inconvenience caused because of the issue.

I had discussion internally and below are the inputs I got :

The way FCI DNN works is that DNN name provides DNS entry to point to IP’s of all VM in FCI,
FCI when detecting DNN resource will listen to all IP bound to the VM for the listener port. The client accessing FCI tries to access all IP’s resolved from DNN and pick the one that succeeds.

Since FCI DNN listens to all IP on this VM so yes if two FCI are deployed on the same set of VM, they need to be on different port to avoid port conflict.
And if a listener is on a non-default port, user need to specify the port in their connection string anyway, which is true for VNN as well.
So for your question, yes, the port 1434 need to be specified in the connection string for 2nd FCI.

VNN with FCI does work. If you have more than one FCI, you have to follow a similar process as we do for multiple AGs to add the IP of the second FCI to the same load balancer example here.

A way to avoid specifying the port is to use sql server browser, which requires opening firewall UDP port 1434. You can check this too.

Hope it helps!!!

Please "Accept as Answer" if it helped so it can help others in community looking for help on similar topics.