Combination of multiple instances and AG listeners - connecting to AG listener sends me to different instance
1
vote
1
answer
782
views
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 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?
Asked by Sranda
(360 rep)
Apr 20, 2023, 01:23 PM
Last activity: Apr 21, 2023, 01:23 PM
Last activity: Apr 21, 2023, 01:23 PM