Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Updating the TCP Port on a SQL Server Cluster Node

MSSQLTips author Thomas LeBlanc By:   |   Read Comments (14)   |   Related Tips: More > Clustering
Problem

When installing 2 SQL Server instances on a Windows Cluster, the installation program requires a Named Instance during the installation of the second node and a dynamic TCP port is created. Our failover procedure for SQL Server instances in a cluster has a DNS name mapped in ActiveDirectory to a specific Cluster Server Name. So, when someone tries to connect to the SQL Server instance by the new DNS name, the connection fails for the named instance because it is not on TCP port 1433. How do you update the TCP port to 1433 after the installation in a clustered environment for the named instance?

Solution

Why change the TCP port on the Named Instance(Second Node)

In a cluster, the physical name and IP are not used. There is a "Virtual IP" that is mapped to a new "Cluster Server Name" that can be referenced by applications. The Named Instance on the second node in a cluster could be used by SQL Browser for name resolution.

But, in our environment, the Networking Group has setup a DNS Server Name mapped to the Cluster Server Name. They do this for DR Failover. The cluster is just used for High Availability in our Data Center. This enables us to use DB mirror to duplicate the databases to an off-site DR facility. If we have a disaster, the networking group can use the DNS to assign the Server Name to a new machine (where the mirrored databases are) without having to re-code the connection string in the applications even though the server has a different IP address. We actually have a single node cluster in the DR site with 2 instances setup the same way.

Setup

Physical Name

IP

Cluster Name

Cluster IP

DNS Name

Instance Name

Srv01 10.0.0.1 SQL01 10.0.1.1 AppSQL1 Default
Srv02 10.0.0.2 SQL02 10.0.1.2 AppSQL2 Inst02

The connection strings either reference AppSQL1 or AppSQL2; they do not have to reference a named instance - SQL02\Inst02. Since the cluster has a new IP for both instances, you can use the same port because the IPs are different for each "Virtual IP and Cluster Server Name". The problem is you cannot use the DNS name with a named instance; it has to be on port 1433 for applications to reference the DNS for SQL Server. So, that is why we can and use the default port for both instances.

Registry In A Cluster

First, to understand registry replication in a cluster, please read this article 174070 Registry replication in Microsoft Cluster Server. In a nut shell, the registry of both instances is kept in sync by the cluster services. So, when you go through the SQL Server Configuration Manager to change SQL Server parameters, the next restart will restore the previous parameter values from the clustering service replication.

 

Microsoft Clustering has a command line utility - cluster .resource. Based on the command line parameters, you can do many things. The options we need here are /removecheckpoints and /addcheckpoints. We used Method 2 from the following article - 953504 How to change SQL Server parameters in a clustered environment when SQL Server is not online.


Step 1

Use cluster .resource to get the SQL Network Name needed to use /removecheckpoint. Run "cluster . resource /checkpoints" at a command line on the node with the named instance. In our case, it was the second node.

 
Step 1 Output

We found the results for us was - SQL Network Name (NamedInstance). The register path was - Software\Microsoft\Microsoft SQL Server\MSSQL11.ServerName

The article above only shows 2008 R2, 2005 and 2000. So, you need to find the 2012 path.

Step 2

Remove the Checkpoint. The above article explains that the Node needs to be online. At a command line prompt on Node 2 we ran:

Cluster . resource "SQL Network name (NamedInstance)" /removecheckpoints:" Software\Microsoft\Microsoft SQL Server\MSSQL11.ServerName"

Step 3

Update TCP port on both nodes in the SQL Server Configuration Manager

 
Step 1 Output

Right-click on the Protocol Name TCP/IP for the Named Instance and select the IP Addresses tab. At the bottom of the properties, delete the TCP Dynamic Port and enter 1433 in the TCP Port. Then, click OK.

NOTE: This must be done on both Nodes even though the instance is only running on one node.

Step 4

Add the check point back. We ran the following at the command line:

Cluster . resource "SQL Network name (NamedInstance)" /addcheckpoints:" Software\Microsoft\Microsoft SQL Server\MSSQL11.ServerName"

Step 5

Failover the instances and check the parameter in the SQL Server Configuration Manager.

Next Steps


Last Update: 5/9/2013


About the author
MSSQLTips author Thomas LeBlanc
Sr. DBA Thomas LeBlanc MCITP 05/08 DBA & 08 BI has spoken at the PASS Summit 2011/12, SQL Rally & many SQL Saturday’s.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, May 10, 2013 - 12:03:32 PM - Jason Read The Tip

I'm curious why you are going through all this effort.  I was able to set up 5 instances on an Active / Passive node to run on Port 1433 on each virtual IP / Name with nothing more than using SQL Server Configuration Manager on the active node.  

 

I too use DNS aliases for all my database connection strings.  That makes it much easier to migrate applications, especially vendor applications where our IT organization might not know how to reconfigure the application.  A simple DNS change does the reconfiguration for it.  I tried to get fancy initally, with a CNAME for the server or instance, and then CNAMES for each application, with the App CNAME pointing to the Server CNAME.  However, that's not a good practice to have CNAMES pointing to CNAMES... so now I have only application CNAMES that point to the A Record of the server.

 

 


Friday, May 10, 2013 - 4:19:52 PM - Thomas LeBlanc Read The Tip

If you are saying that you have 5 instances on ONE node, then you are correct. But if you have multiple nodes, when you fail over the instances the TCP port will return to the original Dynamic Port that was assigned during the installation. This goes for other SQL Server registry settings in a cluster install. The registry is store on the qurom and recovered when there is a failover.

If you are talking about making these changes in ActiveDirectory, I cannot answer the question. I am not the network administrator, and the AD Admins at our office say that cannot direct a IP+TCP to a DNS name. The DNS name will go to the default port (1433) when there is no instance name in the connection string.

It was not much of an effort once you know what you are doing.

Thanks,

Thomas


Tuesday, May 14, 2013 - 1:54:18 PM - Jason Read The Tip

I have 5 instances on an Active / Passive node.  Using only SQL Server Configuration Manager on the active node for each instance allows the change to be made  and "stick".  Now if you have 5 instances each running on a separate node, then I could see where you would not want to RDP to each active instance node and make the change.  But your quote of "NOTE: This must be done on both Nodes even though the instance is only running on one node." is confusing me.  If you change the configuration on the node running the instance you want to change, that's all you need to do... atleast on Windows Server 2008 and SQL Server 2008 and greater.  I've not run SQL 2005 on any clusters.


Wednesday, May 15, 2013 - 10:49:41 AM - Thomas Read The Tip

The note is for people using multiple nodes for cluster failover. If you do not make the same change on each node, when an instance or instances fail to the other node, the TCP port will return to the port created during the installation of a Named Instance.

Thanks,

Thomas


Wednesday, May 15, 2013 - 9:31:29 PM - jfay_dba Read The Tip

Again, in my experience the change only needs to be made on the node that is actively hosting SQL Server.  That will make the needed changes to the registry and those changes will "stick" on restart.  I only have experience with two node clusters, but I have never had to make a TCP Port change on nodes, only the active one.


Tuesday, May 21, 2013 - 7:49:14 AM - crashdan Read The Tip

The cluster quorum has its own version of the registry for these items. Good luck failing over and maintaining those settings. The article states exactly how I have my environment set up. 


Monday, May 27, 2013 - 2:03:58 AM - eugene Read The Tip

HI Thomas

I guess what we are dealling with in an Active/Active cluster of SQL Server. this mean one Windows cluster hosts more than one SQL instance. There are two ways to approach this in my experience. If we allow the setup of dynamic port, then we need to address the non-default instance by its instance name, i.e. SQL02\INS2. If however, if you want to address the INS2 with then virtural SQL name only, i.e. SQL02, then you need to fix the port 1433 as you have stated. But there is one catch: it only works for SQL native client and .Net, ODBC and OLEDB connections will still need to connect as SQL02\INS2. See the bellow thread for more information.

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/4c771629-6c5e-4136-b9b1-199ad53b7fc9

 


Monday, May 27, 2013 - 1:17:29 PM - MIchael C. Ferguson Read The Tip

And ... don't forget, you may also need to update any Kerberos SPNs that are registered using the format specifying the TCP port. http://technet.microsoft.com/en-us/library/ms191153.aspx#Manual

 


Monday, May 27, 2013 - 9:27:16 PM - Thomas LeBlanc Read The Tip

Eugene,

I have not seen this problem you mentioned in our envoironment.

The nodes using this method are SharePoint plus C# applications using entity framework and a Data Warehouse connecting from Cognos and 3rd party ETL tool using JDBC.

Micheal,

We have not used Kerberos, but thanks for the article. I am glad this article has started a discussion of references.

Thanks,

Thomas


Tuesday, May 28, 2013 - 12:10:01 AM - jfay_dba Read The Tip

@eugen, ODBC can connect to a named instance running on port 1433 without specifying the instance name... you have to change the settings when creating the DSN.  Specifically, you have to click the "Client Configuration" button and then uncheck the Dynamically determine port, and then set the port to 1433.  That will allow ODBC to work.


Tuesday, May 28, 2013 - 12:29:19 AM - jfay_dba Read The Tip

@Thomas, this article http://support.microsoft.com/kb/953504 from MS says what i have been saying. If you make the change using configuration manager on the active node(s) then the changes "stick".


Tuesday, May 28, 2013 - 3:04:03 PM - Thomas LeBlanc Read The Tip

JFay_DBA,

The article you are referencing (953504) is the same article I reference in the Registry in a Cluster section of this article. 

 

Registry In A Cluster

First, to understand registry replication in a cluster, please read this article 174070 Registry replication in Microsoft Cluster Server. In a nut shell, the registry of both instances is kept in sync by the cluster services. So, when you go through the SQL Server Configuration Manager to change SQL Server parameters, the next restart will restore the previous parameter values from the clustering service replication.

Microsoft Clustering has a command line utility – cluster .resource. Based on the command line parameters, you can do many things. The options we need here are /removecheckpoints and /addcheckpoints. We used Method 2 from the following article – 953504 How to change SQL Server parameters in a clustered environment when SQL Server is not online.

 

I show using Method 2, not Method 1, because editting the registry can be dangerous.

 

Thanks, Thomas

 


Tuesday, May 28, 2013 - 4:44:19 PM - jfay_dba Read The Tip

Thomas,

 

From the introduction to the article....

 

When you use Microsoft SQL Server 2008 Configuration Manager, SQL Server 2005 Configuration Manager, SQL Server 2000 Enterprise Manager, or SQL Server 2000 Setup to change SQL Server parameters in a clustered environment, you have to make changes on the active node while the SQL Server cluster resource is online. If SQL Server is not online, you have to bring SQL Server online first. However, in some circumstances, you may be unable to bring SQL Server online.

 

I maintain, if you use SQL Server Configuration Manager to make a change to the port of an instance - on the node that is running the instance, it will "stick" when you then failover to another node in the cluster. 

 

The final sentence of the article...

 

This article describes how to change SQL Server parameters in a clustered environment when SQL Server is not online or when you cannot bring SQL Server online.

 

 


Friday, May 09, 2014 - 3:15:45 PM - Bryan Read The Tip

Trying to change default instance port from 1433 to 2009 in FCI.  I have a dedicated NIC IP and the IP I used when I clustered SQL default instance.  When I added the dedicated NIC IP to the SQL Name in the cluster, SQL fails to start if port is anything other than 1433.  Seems odd as I changed all ports to 2009 on all named instances and they have the dedicated NIC IPs.  Anyone have any ideas on this one?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.