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.