31 October,2012 by Jack Vamvas
I developed some scripts to gather information for a Disaster Recovery preparation. Finding and storing the port number used by a SQL Server Instance was a requirement. The SQL Servers are 2012 , which allowed the sys.dm_server_registry DMV to be used. To find earlier versions TCP port information , it is necessary to investigate the Windows registry keys
The sys.dm_server_registry returns SQL Server Instance specific Windows registry details. One of the rows is “TcpPort”.
Invoke-sqlcmd -ServerInstance "MyServer\MyInstance" -Query "select value_data AS Port from sys.dm_server_registry WHERE value_name = 'TcpPort'"