The Right Database Monitoring Tools Make All the Difference
Tuesday, June 26, 2018 - click here to learn more and to register
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 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.
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.
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:
Failover the instances and check the parameter in the SQL Server Configuration Manager.
- Understand the command line utility from the above referenced links
- Read Microsoft's guides on installing Clusters
- Additional clustering tips
Last Update: 2013-05-09
About the author
View all my tips