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.



23 comments:

  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

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

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

    ReplyDelete
  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

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

    ReplyDelete
  6. Can hardly wait for another blog by this essayist.
    192-168.0.1

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  13. 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. 192.168.49.1

    ReplyDelete
  14. This one is great. keep doing awesome!.. whats my ip

    ReplyDelete
  15. OFF PAGE
    I create a cheap rete off page seo WOW, WHAT A EXCELLENT POST. I REALLY FOUND THIS TO MUCH INFORMATICS. IT IS WHAT I WAS SEARCHING FOR.I WOULD LIKE TO SUGGEST YOU THAT PLEASE KEEP SHARING SUCH TYPE OF INFO.THANKS
    SEO LINKBUILDING
    I I create a cheap rete seo link building reading some of your content on this website and I conceive this internet site is really informative ! Keep on putting up.
    BACKLINKS
    I create a cheap rete backlinks seo Megan this is a great way to organize them all!! I have tried to hand write them, but I always forget when they are or what I have linked up. Thank you very much for sharing this with us at TaDa Thursday!!
    NICHE COMMENTS
    here is my great offer i will provide you 80 niche relevant hight quality blog comments with high authorize sites good for your website rank your site on google, bing, yahoo etc. All comments are manually No software use.
    SOCIAL BOOKMARKING
    Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. 
    WEB2.0
    here is my great offer i will provide you 80 niche relevant hight quality blog comments with high authorize sites good for your website rank your site on google, bing, yahoo etc. All comments are manually No software use.
    WEB2.0 PROFILES
    xtremely pleasant article, I appreciated perusing your post, exceptionally decent share, I need to twit this to my adherents. Much appreciated!.
    SOCIAL MEDIA SERVICE
    We are truly thankful for your blog entry. You will discover a great deal of methodologies in the wake of going to your post. I was precisely scanning for. A debt of gratitude is in order for such post and please keep it up. 
    BLOG COMMENTING SERVICE
    I was reading some of your content on this website and I conceive this internet site is really informative ! Keep on putting up.
    BLOG COMMENTING PACKAGES
    Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post.
    HIGH AUTHORITY BACKLINKS
    I am getting this error message in the Promoted Link Tiles itself. In the left navigation its working though. Also I am able to verify the address while adding it in the promoted links.
    HIGH AUTHORITY BACKLINKS


    software company

    ReplyDelete
  16. If you visit other cities and you want to have awesome fun then please meet my friends who are hot and sexy meet these wild beauties for the pleasure you are seeking I assure you that these young women are incredibly sexy and passionate to the degree that you imagine your wildest fantasies will come true hurry up and call them you can know a little about them on this page and then you can choose which one to meet you must grab this opportunity of being with a fine woman with delicate features and romance them so that your life is cherished forever you can go to these towns to have these beauties Watch Series with you and enjoy your life to the fullest just go and book a hotel and then invite these girls to your room for some adventure.

    ReplyDelete
  17. Free Web Directory is an online catalog of websites arrange by categories. Highly specialized SEO friendly human edited free web directory. Submit your websites free with deeplinks. Free Web Directory

    ReplyDelete
  18. Thanks for ones marvelous posting! I really enjoyed reading it, you might be a great author.
    I will be sure to bookmark your blog and will eventually come back later on.
    rubber stamp near me

    ReplyDelete
  19. Once again, an outstanding article! Your talent for simplifying complex concepts makes your content both enlightening and enjoyable.

    patagonia marble in Dubai

    ReplyDelete