Have you ever seen an SQL Server instance configured to listen on both “TCP Dynamic Ports” and “TCP (static) Port”?
This kind of configuration can be caused by the following scenario:
- A named instance is installed. By default, it is configured to use dynamic ports.
- Someone wants to configure the instance to listen to a fixed port and set the “TCP Port” value
- The “TCP Dynamic Ports” is set to value “0” thinking this would disable the dynamics ports
The documentation states that a value of “0” is actually enabling “TCP Dynamic Ports”.
If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports
So what’s happening to SQL Server with this configuration?
What SQL Server is listening on?
Well, I could not find anything related to this particular case in the Microsoft documentation.
If we look at the SQL Server Error Log we can see that the instance is listening on both ports: the dynamically chosen one and the static port.
What TCP port is used by clients connections?
But, are both ports actually used by client connections to the server?
From SQL we can see the established connections and their TCP port using this query:
select distinct local_tcp_port from sys.dm_exec_connections where net_transport = 'TCP'
Looking at this information I see no connection at all using the dynamically assigned port.
Only the static port is used.
My guess is that the SQL Server Browser is giving priority to the static Port and always return this port to clients. I didn’t find any information online about this behavior but it makes sense.
When a client wants to connect to an instance using “server\instancename” an exchange is done with the server using the SQL Server Resolution Protocol using UDP. This is why you should enable UDP port 1434 in your Firewall if you need the SQL Browser.
For details about this protocol, you can read the specifications here.
Doing some tests with Wireshark and a UDP filter we can see the client asking about “inst1”, my instance name.
The server response contains some information about the instance with the most important one, the TCP Port, here the static port: 15001.
I think this configuration should be avoided because it doesn’t seem to add any benefits and could bring some confusion.
If you use a static TCP port for your instance, leave the “Dynamic TCP Port” blank.