0

Background:

To keep things simple, we have a 2 node cluster where each node in the cluster has 3 instances of SQL installed: default + 2 named. I understand instance stacking is bad and it is something we are actively moving away from. 2/3 of these mentioned nodes utilize SQL Server AGs (default & 1 named instance) while the other utilizes FCI for DR (this last instance doesn't really pertain to the question, but adding it anyways). All of our listeners utilize port 1433 because Microsoft had recommended that approach years ago during a migration since adding a non-default port would require including the port number in the listener name when connecting. in hindsight, I am not sure why it wouldn't be possible to simply include the port in the AG listener upon creation since it is obfuscated to the application via the CNAME. I believe we didn't know this at the time and had falsely believed that it would require we add ",port" to every conn string in the company so we chose to stick with 1433 for each listener.

Question:

In the example below, when the default instance of SQL is setup on a non-default port (e.g. 1400), all apps connect as expected. However, when the default port is being used all CNAMES unexpectedly route to 1433 (guessing this is because the listener itself is on port 1433). What I am trying to determine is when a user attempts to connect to 1433, but it does not exist, does the browser identify that no instance with port 1433 exists and so it does some sort of secondary check/resolution logic to correlate the name of the listener itself with the proper instance of SQL?

enter image description here

enter image description here

Found another microsoft doc that explains this scenario, but it fails to explain how SQL is able to relate a listener VNN with the instance itself. It sounds to me like the WSFC resource relates that info to the client to determine which listener routes to which instance. However, if there is an instance on 1433, SQL will improperly route everything to that instance assuming all listeners are setup on 1433 for whatever reason.

"Listener port When configuring an availability group listener, you must designate a port via SSMS. You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. This means, that if you use 1433, you don't need to include a port number in a connection string of your application. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

If you use the default port of 1433 for availability group listener VNNs, you'll still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

If one of the instances of SQL Server is already listening on TCP port 1433 via the instance listener and there are no other services (including additional instances of SQL Server) on the computer listening on port 1433, this won't cause a port conflict with the availability group listener. This is because the availability group listener can share the same TCP port inside the same process. However multiple instances of SQL Server (side-by-side) must not be configured to listen on the same port because one of them will fail to listen for connections.

You can also designate a non-standard availability group listener port. However, you also need to explicitly use the target port in your application connection string when connecting to a listener. You also need to open permission on the firewall for this port.

You can connect to the listener using the name and port (if not 1433). The port can be either the listener port or the underlying SQL Server port that it's configured to listen on." - https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-listener-overview?view=sql-server-ver16

2 Answers 2

1

What I am trying to determine is when a user attempts to connect to 1433, but it does not exist, does the browser identify that no instance with port 1433 exists and so it does some sort of secondary check/resolution logic to correlate the name of the listener itself with the proper instance of SQL?

SQL Browser does not interact, at all, with Availability Groups. Period.

All of our listeners utilize port 1433 because Microsoft had recommended that approach years ago during a migration since adding a non-default port would require including the port number in the listener name when connecting.

This is correct, a non-standard port with an Availability Group will require the use of port numbers.


Response To The OP's Answer

If I am incorrect, please let me know and I will edit my answer.

You are incorrect.

When you have a setup that involves multiple SQL instances on the same machine, and the listeners are configured to run on port 1433, it is imperative that there is no other service listening on port 1433 (e.g. a default instance of SQL).

This is false, no other items can be on the same IP and Port combination. This is basic networking fundamentals. You can use different IP addresses and the same port (see below for example).

If a default instance of SQL is enabled, and the client is making a connection to a listener that uses port 1433, port confliction will occur in that the client will navigate to the host primary (AG listener IP) and then immediately route to 1433 (default instance of SQL) as the SQL Service port takes precedence over the AG listener port.

That's not how it works. First, an error will occur if the same IP/Port combo is attempted to be bound. In SQL Server this will generally throw error 10013: An attempt was made to access a socket in a way forbidden by its access permissions. Second, since the bind attempt will fail, whatever or whomever was listening on that unique IP/Port combo will still have the binding, thus any connections using that will continue to use it.

There is no such thing as "precedence" of ports, AG, SQL, or not. It works as I have explained above.

The Rub

This all comes down to basic networking and proper configuration.

In the below screenshot you can see there are 3 instances stacked on the same server, all with a listener on 1433, all which connect properly. enter image description here

This requires that you configure SQL Server networking correctly, and there are various ways of doing so. One option is to give multiple IP addresses to the network interface or create a network interface per instance. Another way is to tell SQL Server to stop listening on ALL IPs and only listen on some. To show a better SQL Server configuration and because you'd need to do it with the first option, I configured it this way.

Turn Off Listen All Only make certain IPs enabled

Notice that I turned off listen all and enabled only the specific IPs I want. This removes listening on IP ANY, which then stops the attempt to bind to the listener port for each AG on all IPs. Since there is no longer any 0.0.0.0:1433 binding, there is no conflict as each instance has a unique IP:Port combination, including the listener.

To reiterate the entire crux of the OPs multiple questions, SQL Browser does not work with AGs. CNAMEs have no binding or anything to do with Ports. IP:Port combinations must be unique, there is no port duplication magic happening (though you could make a socket duplicator and redirector but that's way out of scope).

0
-1

I believe I have done enough research to better understand what is happening so I am posting my answer here in the hopes that it will benefit somebody else. If I am incorrect, please let me know and I will edit my answer.

As Sean stated, when connecting to an AG via a listener the SQL Browser is irrelevant. When you have a setup that involves multiple SQL instances on the same machine, and the listeners are configured to run on port 1433, it is imperative that there is no other service listening on port 1433 (e.g. a default instance of SQL). If a default instance of SQL is enabled, and the client is making a connection to a listener that uses port 1433, port confliction will occur in that the client will navigate to the host primary (AG listener IP) and then immediately route to 1433 (default instance of SQL) as the SQL Service port takes precedence over the AG listener port. When the default instance of SQL is changed to a port other than 1433, the client making the connection once again connects to the listener IP, and then using the unique TCP IP Address + TCP port, it is able to properly route to the correct instance of SQL.

0

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.