How to change the IP ALL TCP Port to a static port from a Dynamic Port using WMI

How to change the IP ALL TCP Port to a static port from a Dynamic Port using WMI

  • Comments 3

Many a time there is a need to programmatically change in the SQL Server configuration settings. This can be done through the methods exposed via SQL Server’s WMI namespace. The need for this script arose due to a need to change the Port Number of multiple SQL Server installations to a non-default port (1433). The namespace that we will be using here is:

\root\Microsoft\SqlServer\ComputerManagement

This is the name space that is used for management activities for SQL Server 2005. The equivalent namespace for SQL Server 2008 would be \root\Microsoft\SqlServer\ComputerManagement10.

We shall use the ServerNetworkProtocolProperty class to retrieve the current TCP/IP settings for IP ALL and then change the TCP Port for the IP ALL property of the SQL Server instance.

A few gotchas for this script:

1.       The script should be run from active node of the cluster to avoid running into issues with Cluster Checkpointing.

2.       The script needs an input for the TCP Port which should be an integer value.

3.       The default instance is assumed to the instance to be worked upon, if no SQL Server instance name is provided. The short instance name is required. Eg: If your SQL instance server name is SRV1\INST1, then the instance name provided to the script will be INST1.

4.       The script also needs the machine name to whose WMI namespace it connects to. Default is the local box. Even the “.” as an input would assume the local box.

You will have to restart the SQL Server instance for the port changes to take effect.

Sample output of the script:

Please enter the computer name (Enter a . in case of local computer):

Specify the instance name (if any). The current value is set for Default instance:

Specify the new PORT number (Integer value expected):

1435

------------------------------------

Input Parameters:

Compute name: .

Instance name: MSSQLSERVER

Port number: 1435

Are these values correct (Y/N)?:

Y

-----------------------------------

    TCP Port Setting for IP ALL

-----------------------------------

TCP Port Value: 1434

ProtocolName: Tcp

TCP Port changed SUCCESSFULLY changed for instance : MSSQLSERVER

------------------------------------------------------------

  NEW TCP Port Setting for IP ALL         :1435

------------------------------------------------------------

TCP Dynamic Port changed SUCCESSFULLY changed for instance : MSSQLSERVER

SQL Services need to be restarted for the changes to take effect.

The script file is attached. You will have to change the extension of the script file to .vbs before running it. The script can be run in the following manner from a Command Prompt window:

Usage: cscript <filename>.vbs

 

DISCLAIMER: This script is provided "AS IS" with no warranties, and confers no rights. It should be tested before using the same on any environment.

 

 

AMIT BANERJEE
Technical Lead, Microsoft Sql Server

Attachment: SQL_PortChange.txt
Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
  • Is there a WMIC option for this ?

    Any where we can get a WMI to WMIC translator.

  • The same script should be run under Admin privileges for SQL Server 2012.

    Option 1:

    start CMd with "Run as Admin"

    Options 2:

    disable UAC.

  • It doesn't work on SQL Server 2012 R2

Page 1 of 1 (3 items)