SQL Server TCP port

A SQL Server database instance has dynamic TCP port by default, and when installing a instance it is "Best Practice" to set a static TCP port.
Getting the actual TCP port configuration is usual done quickly and manual by SQL Server Configuration Manager, but when I have several installations it is rather cumbersome to logon to each Windows Server to check.
The PowerShell snippet below gets the basic information using SQL Server Management Objects (SMO).

$ComputerName = 'sandbox.sqladmin.lan'
$SsdbName = 'sandy'


$smoComputer = New-Object 'Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer' $ComputerName
if ($SsdbName -eq '.') { # Default database instance
  $smoTcp = $smoComputer.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
else {  # Named database instance
  $smoTcp = $smoComputer.ServerInstances[$SsdbName].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

"ssdb_tcp_port = $smoTcpPortNumber; ssdb_tcp_is_dynamic = $smoTcpIsDynamic"

The variable $ComputerName hold the name of the computer... and the variable $SsdbName holds the name of the database instance. If the instance is default I use the value '.' (dot).
If the script invoked on a named instance with static TCP port, the output is like
ssdb_tcp_port = 54321; ssdb_tcp_is_dynamic = False
and from a named instance with dynamic TCP port the output is like
ssdb_tcp_port = 1930; ssdb_tcp_is_dynamic = True

Allen White: „Accessing WMI Information Using SMO

1 comment:

Chris Cadieux said...

This became very useful in a WAP/SMA deployment we're doing. Thanks for posting the code!