How to check SQL Server Port - SQL Server DBA Tutorial

In this video you will learn how to check SQL Server Port using SQL Server Configuration using SQL Server Configuration Manager as well as using T-SQL Script. It also talks about how to find if SQL Server is using Dynamic Port or Static Port. It demonstrates how to connect to SQL Server instance using IP address and Port number in SQL Server Management studio.

Script to Find out the SQL Server Instance Port

DECLARE @PortNumber VARCHAR(50)
    ,@PATH VARCHAR(100)

IF charindex('\', @@SERVERNAME, 0) <> 0
BEGIN
    SET @PATH = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @@SERVICENAME +
 '\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGIN
    SET @PATH = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'
END

EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @PATH
    ,@value_name = 'Tcpport'
    ,@value = @PortNumber OUTPUT

SELECT 'SQLServer Instance Name: ' + @@SERVERNAME + ' SQL Server Port Number:'
convert(VARCHAR(50), @PortNumber)


How to Get SQL Server Port - SQL Server DBA Tutorial

No comments:

Post a Comment