I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.
This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:
SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement
SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10
So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:
‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.
strInstance = "KATMAI"
‘Specify the new PORT
strPort = "1434"
So, you would have to modify the above variables for the correct Instance Name and Port number.
The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.
If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.
Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.
HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE
Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.
How to- Configure a Server to Listen on a Specific TCP Port
Happy Automation!! 🙂
Just landed on this post via Google seek. I love it. This situation change my percept and I am obtaining the RSS feeds. Cheers.
Thank you for this script. It’s exactly what I was looking for. I need to automatically install sql server instances with a static tcp port value. Now I can do that.
Edwin van der Veeke
Glad to know that it helped.
Does anyone know which class and property should be used to change the “keep alive” setting of the TCP/IP protocol? If I’m not mistaken, it’s not a property of the ServerNetworkProtocolProperty class.
You can check the following:
What other properties can be queried from the ServerNetworkProtocolProperty? I cannot find a reference to this.
I need a script that will pull various parameters of SQL2005 which can be configured. I’m sill kinda new to VBScript and don’t know where to look. If it is a parm that can be set, then I want to query its setting.
The ServerNetworkProtocalProperty online documentation has all the properties and methods list that you can use:
Refer http://msdn.microsoft.com/en-us/library/ms180757.aspx. I will try and do a followup post of all the properties that can be set with sample code.
Pingback: CScript and RunAsAdmin « TroubleshootingSQL