1

The environment in question consists of AD domain and 2 AZ SQL VMs. The SQL server version is SQL 2022 Standard - CU 2. There are currently three DB instances on each AZ SQL VM and databases in those instances work in (basic) AlwaysON AG for non-system databases.

Recently, I cooperated with Microsoft to leverage all the hosting of SQL servers in Azure and their advice, to fully leverage capabilities of Azure IaaS SQL agent is to install default instance in addition to 3 named instances. Which I did.

Now for those 3 instances, the situation is as follows:

  • The SQL server VMs have let's say addresses A,B in their subnet
  • The listeners for each instance and for each DB in that instance have addresses C,D,E,F....
  • The listeners in 2 instances run on TCP ports > 49152
  • The listeneres for last instance run on ports 1433, but on addresses different than A and B

My problem After installing default SQL instance as per advice from Microsoft, when connecting to listener on port 1433, I somehow ended up in the default instance instead of the instance to which the listener running on TCP 1433 belongs.

/* EDIT 01 */ To answer Sean's valuable questions:

To reach the AG listener listening on 1433 (but on different IP to server's address), I have used <Listener's DNS name> and ended up in the default instance - both running on 1433 but with different IPs. Once default instance was uninstalled, all started to work again.

All the named instances listen on dynamic ports, the default instance listens on 1433. On 1433, also listeners of one named instance listen, but those listeners each have individual IP address. The network setup is according to MSFT guideline - Azure load balancer in front of the AZ SQL VMs for AGs to work properly. I did not touch the loadbalancer to reflect the default instance listening on 1433, because that default instance didn't have any AG.

/* End of EDIT 01 */

Does anyone have an idea why?

2
  • What did you use as your connection string? What ports are all of the instances typically listening on? What's the network setup for each instance? Apr 20, 2023 at 13:57
  • Sean, I tried to answer your questions in the edit of my original question.
    – Sranda
    Apr 21, 2023 at 7:29

1 Answer 1

1

You're hitting the same issue as I answered, disregard the browser aspect. Essentially, the instance is set to listen on all IPs by default, and since it's set that way and you have multiple instances, the other ip/port combo's won't be able to bind, thus whomever had the combo first is the one the traffic will be routed.

These are the issues you're going to have by using multiple instances of SQL Server on the same set of servers, to quote myself...

Q8: Can I have more than one instance of SQL Server on each node in an FCI/AG?

Sean: You can, but I wouldn’t. When you mix instances, it makes troubleshooting harder and you get into nit picking for resources. If you do multiple instances, you’ll want to set scheduler affinity, max server memory, I/O governance, etc., so that one instance doesn’t stomp on the other. In reality I’ve yet to see anyone do this correctly or… really… at all… and then spend however long trying to figure out why there is a performance issue on a node that has 7 instances of SQL Server, which of course are all active all the time. I don’t want to deal with that pile-o-crap, so you probably don’t either.

You've also found there are additional considerations when using Azure IaaS with AGs with the ILB (or do what AWS requires which is each node needs to be in its own subnet) which also require unique ports, thus I would not use dynamic ports as you have for the other instances.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.