Setting the SQL TcpPort value via PowerShell and WMI

  • Article

My customer has a requirement that all SQL instances use a non-standard port, and I wanted to set this during install as part of the basic script, because we'll be doing a lot of SQL installs and we want to eliminate manual effort where possible.  I looked around and the few samples I found were using VB Script and were quite verbose and ugly.  I was sure I could do it more simply in PowerShell and it turns out I was right.

 function SetPort($instance, $port)
 {
 $filterString = "InstanceName='$instance' AND IpAddressName='IPAll' AND ProtocolName='Tcp' AND PropertyName='TcpPort'"
 $InstanceList = get-wmiobject ServerNetworkProtocolProperty -namespace "root\Microsoft\SqlServer\ComputerManagement" -filter $filterString
 foreach ($sqlInstance in $InstanceList)
 {
 if ($sqlInstance -ne $null)
 {
 $sqlInstance.SetStringValue($port)
 }
 }
 }
 function SetArgumentVariables ($argList)
 {
 foreach ($arg in $argList)
 {
 $argPair = $arg.Split("=")
 if ($argPair.Count -eq 2)
 {
 $key = $argPair[0]
 $value = $argPair[1]
 switch ($key)
 {
 "port" {
 $result = [System.Int32]::TryParse($value, [ref] $global:Port);
 if (!$result)
 {
 $global:Port = ""
 }
 }
 "instance" {$global:Instance = $value}
 default {}
 }
 }
 }
 }
 SetArgumentVariables $args
 SetPort $global:Instance $global:Port

This can then be called as such:

 powershell .\SetPort.ps1 instance=MyInstance port=9999
 net stop MSSQL$MyInstance
 net start MSSQL$MyInstance