SQL SERVER – SCRIPT – SQL Listening on Dynamic Port?

With every release of SQL Server, sometimes I need to revisit some of the old blogs to see how they have enhanced. This learning experience is an ongoing process and the best way I learn is by doing a search on the blog from time to time. Long ago I had written a blog post to find the port number on which SQL Server is listening: SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Recently one of my blog reader sent email asking below.

I read your blog which talks about reading ERRORLOG and finding the port. But is there any way to find if that port is fixed port or dynamic port?

I know that the port value is stored in registry hive of SQL Server and this is instance specific. Here is the registry key for SQL Server 2014 instance running on my laptop.

If SQL is configured to use Dynamic Ports then TcpDynamicPorts would have the port value. In case of fixed port (which I have on my SQL Instance) then we would see TcpPort value.

To get same information using T-SQL, I have written a small script which would help you. It would detect dynamic port and static port as well.

DECLARE     @ServerName SYSNAME
,@InstanceID NVARCHAR(128)
,@InstanceName NVARCHAR(128)
,@tcp_port NVARCHAR(10)
,@InstanceKey NVARCHAR(255)
SELECT @ServerName = @@SERVERNAME
SELECT @InstanceName = ISNULL((CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128))), 'MSSQLSERVER')
EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
,@value_name = @InstanceName
,@value = @InstanceID OUTPUT
SELECT @InstanceKey = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @InstanceID + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = @InstanceKey
,@value_name = 'TcpDynamicPorts'
,@value = @tcp_port OUTPUT
IF @tcp_port IS NOT NULL
SELECT 'SQL Server (' + @InstanceName + ') uses dynamic tcp port: ' + CAST(@tcp_port AS NVARCHAR(128))
ELSE
BEGIN
EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = @InstanceKey
,@value_name = 'TcpPort'
,@value = @tcp_port OUTPUT
SELECT 'SQL Server (' + @InstanceName + ') on ' + @ServerName+ ' uses static tcp port: ' + CAST(@tcp_port AS NVARCHAR(128))
END
GO

Generating such scripts takes time and the best way to learn these is by sharing. So do you have any other script similar to this that is worth a share for other readers? Please share them via the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)