다음을 통해 공유

How a tiny little whitespace can make life difficult for your SQL Cluster

  • 아티클

Remember that tiny little whitespace that we tend to ignore most of the time? Believe it or not, there are situations when you could pay heavily if you don’t pay attention to this itsy-bitsy little character. Let me explain how:

If you have a SQL Server instance, or multiple ones, on a cluster, and decide to have all of them running on the same static ports (on different IP’s, of course), then you might be surprised to see some of the services failing to come online after the change. The reason? Read on.

When we change the port from SQL Server Configuration manager (SQL Server Network Configuration->Protocols for InstanceName –>TCP/IP->Properties), typically we just remove the value for the TCP Dynamic Ports under IPAll, and enter the static port number in the TCP Port field. A value of 0 in the TCP Dynamic Ports field indicates that Dynamic ports are to be used. By default, the SQL installation uses dynamic ports, and except in the case of a default instance, the static port field is empty.

Coming back to the topic, say, after we change the port settings to reflect the static port number, we restart the service and it fails to come online. Check the errorlog, and you might see something like this:

2012-05-17 13:08:29.34 Server      Error: 17182, Severity: 16, State: 1.
2012-05-17 13:08:29.34 Server      TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol's 'IPAll' configuration key. The data is invalid.

2012-05-17 13:08:29.35 Server      Error: 17182, Severity: 16, State: 1.
2012-05-17 13:08:29.35 Server      TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

So, the error says the data in the IPAll configuration key is invalid. Where exactly is this key anyways? The TCP protocol, and the IPAll subkey, are located in :

HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL Server <InstanceName> MSSQLServerSuperSocketNetLib

Under the IPAll subkey, you will find the same two “TCP Dynamic Ports” and “TCP Port” keys. Check the value for the TCP Dynamic Ports key. Do you see a whitespace there? If so, then most likely that is the reason for the service startup failure. Removing the whitespace should fix the issue, and the service should come online just fine. This is equivalent to changing it from the SQL Server Configuration manager, and the registry should only be used when you cannot access the SQL Server Configuration Manager for some reason. 

Hope this helps.

Comments

  • Anonymous
    November 13, 2013
    The comment has been removed

  • Anonymous
    November 13, 2013
    You're welcome Andrew...glad to know it helped...!!!

  • Anonymous
    March 17, 2014
    This hint saved my face today ... Thank you.

  • Anonymous
    March 19, 2014
    Hi DVP, happy to be of help. Thanks for appreciating.

  • Anonymous
    May 05, 2014
    Thanks for you granular check...it worked for me

  • Anonymous
    May 06, 2014
    Thanks for appreciating Anil...!!!

  • Anonymous
    August 04, 2014
    The comment has been removed

  • Anonymous
    August 08, 2014
    Excellent - saved me a lot of time tracking this down.

  • Anonymous
    August 18, 2014
    Thanks a lot for appreciating Varma. Happy to know it helped.

  • Anonymous
    August 18, 2014
    Thanks a lot for appreciating John...happy to know it saved you some time and effort.

  • Anonymous
    September 16, 2014
    Thanks a lot , my server is running again.

  • Anonymous
    September 18, 2014
    Thanks a lot benny....I'm glad the solution worked for you.