AlwaysOn Availability Groups: connecting correctly?

Question

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

Answer 1

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.

Answer 2

There's some weirdness when you use AG Listeners along with multiple standalone SQL instances. Note that the more common configuration is to install a single standalone instance per node, possibly participating in multiple AGs.

When you enable TCP/IP connections for a SQL instance, the default configuration is for the SQL instance to listen on a port (either fixed or dynamic) on "All IP Addresses":

For standalone instances "Listen All" means that it will listen for incoming connections on all current and future IP addresses. If an IP address is later added to the system, the instance is already listening on its port on the new IP. And an AG Listener is just a Network Name and IP that the cluster adds to whichever node hosts the primary replica.

For Failover Cluster Instances, "Listen All" means that the instance will listen on all IP addresses that are dependencies of the FCI's Virtual Network Name, so they don't have this problem.

So, when you have multiple standalone instances where one or more participates in an AG with an AG Listener, you need to perform some extra configuration to prevent every instance from listening on every AG Listener's IP.

Configure each instance to listen on a non-default port (can be dynamic), only on the host's main IP(s), reserving port 1433 for the AG Listener(s). That way you can connect to each AG Listener without a port number or instance name, and to the instances directly only with a port number or instance name.

Answer 3

How do I correctly connect to the primary replica of AG1 via the listener?

In your case, you'll connect to Listener1 using the proper port. Since your ports are all 1433, just using the name is sufficed.

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 …

Listeners don't engage SQL Browser, any connection attempts using an instance name with the listener that go to the "proper" place are happenstance. Using a combination of the listener name and port is the proper way to connect as it will uniquely identify each instance.