PowerShell: SQL Server Dynamic Port Instance

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
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.