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?
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
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
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.
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.
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.
Remove the Checkpoint. The above article explains that the Node needs to be online. At a command line prompt on Node 2 we ran:
Update TCP port on both nodes in the SQL Server Configuration Manager
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.
Add the check point back. We ran the following at the command line:
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
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.
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.
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.
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.
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.
@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.
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.
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.