Sunday, October 7, 2007

What? I lost my port on a named instance?Dynamic port detection?

One thing I realized is that SQL Server port numbers will not be the same for I don't know what reason. I found this out while rebuilding a SQL Server named instance. We had standard settings in setting up SQL Server. But since I am rebuilding my test environment, I just used the default port number settings (which is 1433) during the process. To my surprise, it was using port number 3080 when the service started. Apparently, SQL Server dynamically determines the port it listens on. It will try to listen on the port it used previously. If for some reason it cannot bind to that port, the named instance may bind to a different port - which is obviously the case for my setup. Now this would cause a lot of problems if your SQL Server is interacting with a lot of servers, services and applications as you need to either change the port number to it's original value (which I think would be more appropriate) or change the clients to use the new port number. I have yet to dig up more details on this as to why SQL Server behaves that way. At least I know another place to look at when troubleshooting connectivity problems

I was doing more research on SQL Server port usage when I chanced upon this Microsoft KB. SQL Server clients use DBNETLIB to perform port detection. This is either thru ODBC or SQLOLEDB componentes which loads DBNETLIB. SQL Server clients can use the Client Network Utility for dynamic port detection. This is to make sure that in case SQL Server uses a different port number during service startup or is configured to user dynamic port assignments, the client can map to the port number that a specific instance uses. Now, what if the client is a third party application running on a non-Microsoft platform(like a JSP application running on top of Apache on a Linux box?) How does this port resolution work?

No comments: