SQL Server TCP: Having both Dynamic Ports and Static Port configured

Introduction

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:

1. A named instance is installed. By default, it is configured to use dynamic ports.
2. Someone wants to configure the instance to listen to a fixed port and set the “TCP Port” value
3. 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

After a service restart, SQL Server will listen to a port like 50119 for example.
You end up with the following configuration.

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.

We can confirm this by trying a connection using SSMS:

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'

This could also be seen with netstat:

Looking at this information I see no connection at all using the dynamically assigned port.
Only the static port is used.

SQL Browser

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.

Conclusion

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.