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 (12)

  1. Andrew says:

    Bang on, thanks for the info, got me running again:-)

  2. HarshDeep_Singh says:

    You're welcome Andrew…glad to know it helped…!!!

  3. DVP Rao says:

    This hint saved my face today … Thank you.

  4. HarshDeep_Singh says:

    Hi DVP, happy to be of help. Thanks for appreciating.

  5. anil says:

    Thanks for you granular check…it worked for me

  6. HarshDeep_Singh says:

    Thanks for appreciating Anil…!!!

  7. Varma says:

    Harsh, thanks for the blog, today I was able to resolve the issue bang on, customer was really happy.

  8. John Lane says:

    Excellent – saved me a lot of time tracking this down.

  9. HarshDeep_Singh says:

    Thanks a lot for appreciating Varma. Happy to know it helped.

  10. HarshDeep_Singh says:

    Thanks a lot for appreciating John…happy to know it saved you some time and effort.

  11. benny s. says:

    Thanks a lot , my server is running again.

  12. HarshDeep_Singh says:

    Thanks a lot benny….I'm glad the solution worked for you.