Automation to list all TCP static and dynamic ports of SQL Server instances using PowerShell

I was asked by the reader on how to get all the port information of SQL Servers instances. As you all know that PowerShell is built on .Net automation framework. I would prefer PowerShell to design any such requirements than any other languages.

In this post, I’ll show to how to gather the information without logging on to each server.

Prerequisites

• PowerShell 3.0 or above
• WMI Service is enabled on all the target machine

The Server List is fed to the PowerShell script as an Input and output lists the ServerName, InstanceName, TCPPort and it’s the corresponding value

Method 1: Iterating over the Input file

The input file Server.csv contains the list of servers

For example,

• 

Now, run the following PoSH script. In the script, you need to change the Inputfilename parameter.

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values  
$Results=@() 
 #the servers are listed. Make WMI service is enabled on all the target machine  
#Import the server list - Read the servers from the c:\server123.csv file.  
Import-Csv $Inputfilename |% {  
$namespace = Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1 
$port=Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName |   where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''}  foreach ($p in $port)  
   {  
     $Properties = @{      
                     Servername = $_.Server      
                     Instancename = $p.instanceName 
                     PropertyName = $p.PropertyName
                     port=$p.propertystrval  
                 }  
$Results += New-Object psobject -Property $properties  
   }  
}  
$Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

Output

• 

Method 2: Direct feed of Servers as an array

In some scenarios where you don’t prefer to take input from a file. In this case, you can directly feed the server names as an array to the script.

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values  
$Results=@()
 'hqdbt01','hqdbsp18','hqdbsp17'|%{
 $namespace = Get-WmiObject -ComputerName $_ -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1 
$port=Get-WmiObject -ComputerName $ -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName | where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''}
 foreach ($p in $port)
 {
  $Properties = @{
     Servername = $_
     Instancename = $p.instanceName
     PropertyName       = $p.PropertyName
     Port=$p.propertystrval
 }
 $Results += New-Object psobject -Property $properties
 }
 }
 $Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

•