SQL Server – Find SQL Server tcp port with Powershell

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”.

The Powershell Invoke-sqlcmd cmdlet is useful when using T-SQL or XQuery and  sqlcmd is the preferred method of connecting to the SQL Server.

 

Invoke-sqlcmd -ServerInstance "MyServer\MyInstance" -Query "select value_data AS Port from sys.dm_server_registry WHERE value_name = 'TcpPort'"

 Related Posts

TCP Chimney offload - SQL Server DBA

Error 26073 and TCP connection closed - SQL Server DBA

Powershell and Disaster Recovery preparation – T-SQL

Difference between TCP and UDP network protocols


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Find SQL Server tcp port with Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer