Updating the TCP Port on a SQL Server Cluster Node

By:   |   Comments (17)   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, April 17, 2024 - 11:21:41 PM - Gopikrishna Back To Top (92187)
I have a SQL Server Failover CLuster with 2 nodes using Default port. I want to change them to non default port. Do we have any detailed steps for that?

Friday, July 13, 2018 - 4:45:38 AM - Luka Gros Back To Top (76631)

 Great post! Helped me alot. I did found out, that if you set this up in single node SQL cluster it replicates registry without needing to remove/add checkpoints, when installing the second (or any other) node :)

Wednesday, January 17, 2018 - 7:01:19 PM - Ashwani Back To Top (74981)

 

Hi,

There is another method to resolve it as follows:

1. Open configuration manager on Active Node -- Goto TCP/IP properties of SQL instance --> on Protocol Tab change "Listen All" to No

2. Go to Ip Addresses tab and set TCP Port (IPALL) to 1433 or yourchoice. Remove TCP Dynamic Port.

3. pause SQL instance in cluster manager

4. come back to Consgfiguration manager and restart the SQL instance/agent. Once SQL services are up, it should also come up in cluster manager

5. Connect to SQL instance and check errorlog for port change. you will be happy :)

now repeat Step 1, 2 only on another node and failover and Failback SQL instance. All good.

 

 

Friday, May 9, 2014 - 3:15:45 PM - Bryan Back To Top (30707)

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?

Tuesday, May 28, 2013 - 4:44:19 PM - jfay_dba Back To Top (25172)

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.

 

 

Tuesday, May 28, 2013 - 3:04:03 PM - Thomas LeBlanc Back To Top (25171)

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 - 12:29:19 AM - jfay_dba Back To Top (25160)

@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 - 12:10:01 AM - jfay_dba Back To Top (25159)

@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.

Monday, May 27, 2013 - 9:27:16 PM - Thomas LeBlanc Back To Top (25156)

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

Monday, May 27, 2013 - 1:17:29 PM - MIchael C. Ferguson Back To Top (25153)

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 - 2:03:58 AM - eugene Back To Top (25148)

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

 

Tuesday, May 21, 2013 - 7:49:14 AM - crashdan Back To Top (24065)

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. 

Wednesday, May 15, 2013 - 9:31:29 PM - jfay_dba Back To Top (23972)

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.

Wednesday, May 15, 2013 - 10:49:41 AM - Thomas Back To Top (23963)

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

Tuesday, May 14, 2013 - 1:54:18 PM - Jason Back To Top (23950)

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.

Friday, May 10, 2013 - 4:19:52 PM - Thomas LeBlanc Back To Top (23887)

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

Friday, May 10, 2013 - 12:03:32 PM - Jason Back To Top (23879)

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.

 

 














get free sql tips
agree to terms