This question is about the correct way of accessing an Availability Group Listener:
Assume I have two AlwaysOn Availability Groups with the following AG's: AG1 and AG2.
I also have 2 listeners called LISTENER1 (on AG1) and LISTENER2 (on AG2).
I have 2 SQL nodes NODE1 and NODE2 and both have 2 SQL instances running: INSTANCE1 and INSTANCE2.
How do I correctly connect to the primary replica of AG1 via the listener?
I know it's not supposed to be NODE1\INSTANCE, but which of the following is correct?
LISTENER1
or
LISTENER1\INSTANCE1
I noticed both work fine and I assume the latter connects to the primary replica of LISTENER1 and than lets SQL Browser do its magic and connects to INSTANCE1 (which – by luck – happens to be correct in this particular setup).
I noticed if I use LISTENER1\INSTANCE2, this works as well, BUT connects to the INSTANCE2 instance of the node which hosts the primary replica of AG1!
The rabbit hole goes even futher: if I install a default instance on both SQL nodes I get yet another behavior: Connecting to LISTENER1, connects me to the default instance.
Is my logic correct (SQL Browser service) or am I just completely wrong here?
FYI: Named instances in this case use dynamic ports and all listeners are hosted on port 1433
Best Answer
If your connection string includes a \ then you are connecting to the node via the browser and not the availability group which sort of defeats the whole reason for having an AG in the first place. In truth there are a few funnies with connections and my thoughts are that it is probably best to disable the sql browser service and connect with listener name and port, that way you are using the AG as intended. ( If you have your instances and the listener on different ports you can query your connections to see which port is being used. ) I'd also suggest you don't give your listener the same name as your AG as this can muddy the waters further.