1

I have the following script to find out what is the Static and dynamic port numbers:

--===============================
-- sys.dm_exec_connections
--===============================
SELECT 
[user]=suser_sname()
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

--===============================
-- Execute below script if SQL Server is configured with dynamic port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

--===============================
-- Execute below script if SQL Server is configured with static port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

enter image description here

The following link mentions netstat -ano to check usage from the cmdline.

When is a Dynamic Port “dynamic”?

when is the dynamic port used?

1 Answer 1

1

Dynamic ports are used when a SQL Server instance is configured to use dynamic ports on the specified interface(s). By default, only named instances use dynamic ports as described in the documentation. Below is an excerpt from the doc:

At startup, when an instance of SQL Server is configured to listen on dynamic ports, it checks with the operating system for an available port, and opens an endpoint for that port. Incoming connections must specify that port number to connect. Since the port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance. Using dynamic ports complicates connecting SQL Server through a firewall because the port number may change when SQL Server is restarted, requiring changes to the firewall settings. To avoid connection problems through a firewall, configure SQL Server to use a static port.

The SQL Server error log will also include messages indicating the listening interface IP addresses and ports.

EDIT:

As mentioned by @TiborKaraszi, SQL Server remembers that last used dynamic port and will continue to use that as the going forward unless there's a conflict, in which case a new one is used.

2
  • 3
    Just a note that SQL Server will not change the port at each startup for "dynamic port" numbers. It remember what port was used last, in the registry and will attempt to use that same port at next startup. If that port isn't available (not likely, at all), then it will use some other port (the "dynamic" part). In contrast to a "static" port - which will refuse to start if that port isn't available at startup. Jun 13, 2018 at 12:40
  • Good point @TiborKaraszi. The doc doesn't call that out so I'll add that to my answer.
    – Dan Guzman
    Jun 13, 2018 at 13:06

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.