How to check SQL Server Port Number using T-SQL Scripts
It
could be difficult to find the port used by the database engine if you have
multiple instances of SQL Server running on the same server or if you have
modified the default port.
How can
I check which TCP/IP port a given SQL Server instance is running on? In
this tip, we'll discuss different methods a database administrator can use to
identify the port a SQL Server instance is using.
Below script is quite helpful to DBAs when managing many SQL instances.
SET NOCOUNT ON
DECLARE
@Port VARCHAR(30)
,@Key VARCHAR(150)
IF CHARINDEX('\',@@SERVERNAME,0)
<>0
BEGIN
SET @Key = 'SOFTWARE\MICROSOFT\Microsoft SQL
Server\'
+@@SERVICENAME+'\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGIN
SET @Key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer
\Supersocketnetlib\TCP'
END
EXEC master.sys.xp_regread
@Rootkey='HKEY_LOCAL_MACHINE'
,@Key=@Key,@value_name='Tcpport'
,@value=@Port OUTPUT
SELECT 'SQL Server Name: '+@@SERVERNAME + ' Port # '+CONVERT(VARCHAR(20),@Port)
Result:
0 Comments