The following script will get the listening port of a dynamic instance name on Microsoft SQL Server
function Get-SQLInstancePort ([String]$ComputerName="SQL01",[String]$InstanceName="EthHack") { [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') $smoComputer = New-Object 'Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer' $ComputerName if ($InstanceName -eq '.') { # Default database instance $smoTcp = $smoComputer.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'] } else { # Named database instance $smoTcp = $smoComputer.ServerInstances[$InstanceName].ServerProtocols['Tcp'] } if ($smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value -eq '') { [bool]$smoTcpIsDynamic = $false [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value } else { [bool]$smoTcpIsDynamic = $true [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value } $smoTcpPortNumber }
Very nice, helped document a large number of SQL Servers in anticipation of a planned upgrade. Thank you Chase!