Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using TSQL, how can I find if the MS SQL server (any version) is configured to use static port or is it using dynamic port?

Script i am trying to use:

DECLARE @TcpPort VARCHAR(5)
        ,@RegKey VARCHAR(100)

IF @@SERVICENAME !='MSSQLSERVER'
    BEGIN
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    END
    ELSE
    BEGIN
        SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP\IPAll'
    END

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpPort'
    ,@value = @TcpPort OUTPUT

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpDynamicPorts'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
        ,@@SERVERNAME AS ServerName
        ,@@SERVICENAME AS ServiceName
share|improve this question
1  
For your @@SERVICENAME key, remove the \IPAll from the end of the string. –  Mark Storey-Smith Oct 18 '11 at 14:27
    
Thanks heaps!!!! –  Manjot Oct 25 '11 at 22:07
add comment

2 Answers

up vote 8 down vote accepted

You can determine which port an instance is using from the error log but not whether its static or dynamic.

EXEC xp_readerrorlog 1, 1, 'Server is listening'

Alternative would be to read from the registry via xp_regread.

DECLARE 
    @StaticPort NVARCHAR(512)
    , @DynamicPort NVARCHAR(512)

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpPort'
    , @StaticPort OUTPUT

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpDynamicPorts'
    , @DynamicPort OUTPUT

SELECT
    @StaticPort AS StaticPort
    , @DynamicPort AS DynamicPort
share|improve this answer
    
xp_regread: is it present in sql server 2008? –  garik Oct 17 '11 at 8:43
2  
Undocumented and unsupported but yes it is there. –  Mark Storey-Smith Oct 17 '11 at 10:01
    
+1 Thanks for information. –  garik Oct 17 '11 at 10:53
    
@MarkStorey-Smith I tried to generalize this for multiple SQL servers but I don't see anything. Did i miss something? Script has been appended to the question –  Manjot Oct 17 '11 at 22:28
    
@Manjot, daft typo in my script above, now corrected. Prefer your version by the way, have filed it away in the script library. –  Mark Storey-Smith Oct 18 '11 at 14:25
show 1 more comment

I think, it is possible (theoretically):

share|improve this answer
    
thanks..yes Use static, dynamic port allocation what and how to read from registry sounds appropriate... but how can I know which key to read? –  Manjot Oct 17 '11 at 0:09
    
@Manjot see SQL Server xp_regread with T-SQL –  garik Oct 17 '11 at 7:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.