Introduction
Speak guys !!
In this article I would like to share with you what I explained in a Telegram group, which is how SQL Server dynamic ports work, which many people have the misconception of how it works.
While some people think that SQL Server has assigned a new port each time the service is started, others already think that this process only happens at first startup. I will demonstrate in this article what is the actual behavior of SQL Server with respect to dynamic and static ports.
If you want to discover the current SQL Server port, be sure to visit my article. How to identify the port used by the instance of SQL Server.
To better understand how SQL Browser works and what it is for, read the article SQL Server - How to Connect Using Dedicated Admin Connection (DAC) Connection Without SQL Browser.
How SQL Server Dynamic Port Works
The first step is to verify that SQL Server is configured to use dynamic or static ports. To do this, simply open SQL Server Configuration Manager:
If the “TCP Dynamic Ports” field in SQL Server Configuration Manager is set to 0 (zero), it is because SQL Server is configured to use dynamic ports and is the first service startup (or you just changed this setting). I will restart the SQL service to see what happens.
After restarting the SQL Server service, let's check what happened to our SQL Server port:
The TCP Dynamic Port (TCP) dynamic port has been changed from 0 to a random port that SQL Server requests from the OS (in this case, 55043 port).
The number of this port is written to the Windows registry:
If I restart SQL Server again, we will see that the dynamic port number has not been changed:
Upon return of the OS, SQL will store the port number in the Windows registry and will always use that port the next time the service is started. But is it always the same?
After Using the SQL Port in Another Process
Now I want to understand how SQL Server behaves when the service tries to start and the port selected the first time the service was started is already in use. To do this, I will stop all SQL Server services and change the service of the instance “SQLEXPRESS” to use the port of the other instance (55043):
After starting the SQLEXPRESS service and then SQL2017, we can notice that the SQL2017 instance port number was regenerated and associated with another port number:
Upon return of the OS, SQL will store the port number in the Windows registry and will always use that port the next time the service is started until one day this port is already in use by another process. If this happens, the dynamic port number returns to 0 and a new port will be assigned to this instance of SQL Server by the Operating System.
Define a static port
If you want to stop using a dynamic port on SQL Server and start using a static port, just configure it in the previous screen so that the “TCP Dynamic Ports” field is empty (no value filled) and in the “TCP Port” column you define which port this instance will be active and waiting for connections:
Remember that using a static port, SQL Server will always try to use the same port, defined by you. If the chosen port is in use, the SQL Server service will return an error (which can be found in SQL ERRORLOG):
Error message transcript:
TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP / IP listener.
TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the operating system error log for information about possible related problems.
Static port x Dynamic port
One of the biggest dilemmas for newcomers to SQL Server is the definition of static X dynamic port. I will prepare a summary to facilitate in your decision:
| Dynamic port (TCP Dynamic Ports) | Static Port (TCP Ports) |
|---|---|
| It has value "0" in the field "TCP Dynamic Ports" at first startup, fixed port number (randomly generated) after first run. Returns to 0 value if previously assigned port is in use. | Fixed value in user-defined "TCP Ports" field (and "TCP Dynamic Ports" field is blank) |
| Port may change if busy at service startup (although not so common) | Port does not change even if it is busy at service startup (will give error) |
| Firewall rules must be redone if the port changes. And you should wait for the service to go up to identify which port will be associated with the service at first startup. | Firewall rules are set up only once, and do not change anymore. In addition, they can be configured even before installing SQL Server because the port number can already be set before installing. |
| Used by default for named instances (eg localhost \ sql2017) | Used by default for the default instance (eg localhost) |
| There is no default port | Default port 1433 for default instance |
That's it folks!
A big hug and even more.
