When is a Dynamic Port "dynamic"?

Question

I was having a discussion today about Dynamic Ports with one of my co-workers and could use some help clarifying how they work.

First question: If the IPALL TCP Dynmaic Ports setting is a specific number (say 1971) does that signify that you have a static port of 1971 or a dynamic port that is currently 1971 and may change at some point in the future.

Second question: This is the one I'm most curious about. We have an instance that has had the same port (the value in the IPALL TCP Dynmaic Ports setting) for several years through dozens of instance restarts. What actually causes the dynamic port to actually change after an instance restart?

Answer 1

This KB article : How to configure SQL Server to listen on a specific port will clarify things for you :

Dynamic Port Allocation

If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

TCPDynamicPorts = Blank

TCPPort = 0

However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the instance of SQL Server, the registry values are set as follows:

TCPDynamicPorts = Current port that is used

TCPPort = Current port that is used

Static Port Allocation :

If you configure an instance of SQL Server to use a static port, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

TCPDynamicPorts = Last port that is used

TCPPort = New static port to be used after the next restart; new static port that you set by using the Server Network Utility

However, if you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the registry values are set as follows:

TCPDynamicPorts = Blank

TCPPort = New static port that you set by using the Server Network Utility

for your second question -

Every time you start the named SQLServer, it uses the port that is allocated. In case if the port is used by another program, then SQL Server chooses another port at the time of restart i.e. The dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if its used by another program then SQL server will choose a new port. note: For Prod Servers, I use only static ports - security and ease of manageability reasons.

Note: More cools thing to find out :

Check if Dynamic port is being used or not using T-SQL :

SELECT NAME
    ,protocol_desc
    ,type_desc
    ,state_desc
    ,is_admin_endpoint
    ,port
    ,is_dynamic_port
    ,ip_address
FROM sys.tcp_endpoints

You can use netstat -ano to check using cmdline.

Additionally, you can check what got cached earlier in the registry on the client side as to what port it is using to connect to sql server :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\LastConnect