How to change the IP ALL TCP Port to a static port from a Dynamic Port using WMI
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