How to change the TCP/IP port for a SQL Server instance

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.

Download the script from here

Reference:

How to- Configure a Server to Listen on a Specific TCP Port

Happy Automation!! 🙂

March Architect Innovation Cafe Webcasts-Register Today!

 

image

March 25, 2010 at 1:00pm – 2:00pm EST

Title: Extending Your Sites Reach with IE8 Add On Features

Abstract Today’s users are getting more sophisticated and they expect more features from the sites and services they use. In this session learn about how to implement low effort, high value add-ons that expand your sites reach and bring value to your customer base .

Link to Register: https://swrt.worktankseattle.com/webcast/4240/preview.aspx

Presenter:
Jim Cirone
Architect Evangelist, Microsoft

Jim Cirone is an Architect Evangelist with Microsoft’s Developer and Platform Evangelism team. He joined the DPE team 3 years ago after spending 10 years in Microsoft’s services group architecting and delivering complex solutions. In his current role he is focused on new and emerging web technologies.

March 26, 2010 at 1:00pm – 2:00pm EST

Title: Windows Azure Design Patterns

Abstract: One of the challenges in adopting a new platform is finding usable design patterns that work for developing effective solutions. The Catch-22 is that design patterns are discovered and not invented. Nevertheless it is important to have some guidance on what design patterns make sense early in the game.

This webcast attacks the problem through a set of application scenario contexts, Azure features and solution examples. It is unique in its approach and the fact that it includes the use of features from all components of the Windows Azure Platform including the Windows Azure OS, Windows Azure AppFabric and SQL Azure. In this webcast you will learn about the components of the Windows Azure Platform that can be used to solve specific business problems.

Link to Register: https://swrt.worktankseattle.com/webcast/4241/preview.aspx

Presenter:
Bill Zack
Architect Evangelist, Microsoft

Bill Zack is an Architect Evangelist with Microsoft. He comes to this role after serving as a Solutions Architect in the Financial Services Unit of Microsoft Consulting Services. His experience includes developing, supporting and evangelizing .NET/SOA based frameworks used to jump-start development projects for financial services companies.

Stay Connected:

clip_image002 clip_image004clip_image006