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!