Tuesday, 10 July 2012

Changing IP Settings on an SQL Cluster

This simple five minute job during an implementation  really threw me.

To paint a picture there is a three node SQL cluster with two instances (2 Active nodes, one Passive) these are isolated from clients behind a firewall.

To facilitate a hardened firewall policy to permit only TCP 1433 to the instance resource group IP addresses, as well as  ensuring only the instance resource group IP address listens on that port (opposed to the default ALL IP’s setting) some changes are required to the network settings in SQL configuration manager.

On a standalone SQL server, it’s simply a matter of changing the settings using the Configuration Manager GUI, restarting the SQL service and the change takes effect. However when in a cluster the changes revert back to the previous ones immediately after clicking ok.

After venturing into this issue a bit more I discovered what I was trying to do wasn’t really documented any ware, but some other articles pointed me in the general direction of the joys of quorum in clustering. In a nutshell I was making a change on one box but as the registry settings being changed are managed by the cluster service the the other two nodes in the cluster won quorum and overwrote the settings.

To change these settings the cluster the reservation checkpoint for the registry path needs to be removed, the changes made in the registry and then the cluster reservation checkpoint added again.

The first step is to get the checkpoint name of the instance you are going to modify, run the following command:

Cluster res /checkpoints

Once you have the instance name, take the SQL server offline in failover cluster manager and run the following command:

cluster res "SQL Server (INSTANCENAME)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQLSERVER"

You should now edit the registry or use SQL configuration manager to make the changes you wish to make.
Personally I prefer to edit the registry as this enables you to delete the unused IP addresses and just leave the cluster IP in place, which is much tidyer.
The path to edit the registry settings is

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCENAME\MSSQLServer\SuperSocketNetLib\Tcp\

Delete any of the IPx keys you don’t need but leave IPAll
To specify the port for the IP address to listen on, simply modify the TCPPort value, and remove the value from the TcpDynamicPorts

Once you are happy with the changes, run the following command to add the checkpoit back into clustering

cluster res "SQL Server (INSTANCENAME)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQLSERVER"

Bring the SQL Server resource back online and check SQL configuration manager, the changes should have taken affect.

As a result of this change, your firewall rules will be more secure as the massive dynamic port range doesn’t need to be permitted and if need be both SQL instances can be failed over to the one server without ports comflicting. There is also the added bonus that the IP configuration in SQL configuration manager looks a whole lot tidyer.


  1. When you choose a web address, you want to make it descriptive of your company name, or the service you offer. British IP Address

  2. My first idea was to compose an article pretty much MAC addresses and remote Ethernet myip

  3. To such people, an IP deliver database is thought to be a pivotal business streamlining apparatus Wearable Tech Blogs

  4. I remember the words: I am the master of my fate, the captain of my soul. What does Henry David Thoreau want me to understand: I captain my soul and I master my fate?ip stresser

  5. Your firewall setting ought to be done in a way that will enable your VPN to stream freely. http://whatsmyrouterip.com/

  6. Can hardly wait for another blog by this essayist.

  7. This is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work. https://192-168-i-i.com

  8. The article you have shared here very awesome. I really like and appreciated your work. I read deeply your article, the points you have mentioned in this article are useful En savoir plus

  9. A simple bookmarking tool that makes it easy to save, organize and share your favorite web pages. Access your bookmarks from any computer, phone or tablet. Listango works on all modern web browsers… Klik hier

  10. Its an incredible joy perusing your post.Its brimming with data I am searching for and I want to post a remark that "The substance of your post is magnificent" Great work.  Klik hier

  11. There are a number of plays of food show. This show is the greater podcast that can be watched at YouTube also. You would also like to listen about the Company where I do job which provides best creative writer India which can make your show more popular. privacy in the network

  12. Great info! I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have. Privacy Online

  13. I'm really impressed about the info you provide in your articles. If you want to invest in suits and be stress-free, then you should shake hands with us. We are providing great clothing in the most reasonable price range that you wouldn’t find anywhere else. Put your trust in us as your supplier since we are the prominent custom stickers printing You can rely on us for the clothing problems we assure you we will never let our customers down.

  14. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our.

  15. Grandstream PBX System- Grandstream Distributor Cameroon VDS the Grandstream distributor in  Cameroon continues to bring innovative Grandstream Products to the IP communications / Telephony market with compelling values and features. Grandstream Networks is headquartered in Brookline, Massachusetts with offices in Yealink IP Phones