SQL Server - TCP/IP Ports
Personally I think networks are a black art, I think
there are probably less network admins that there are DBAs in the world. As for
diagnostics and tools for network configuration, as a SQL Server person I feel I
now know what its like configuring and running Oracle.
My latest venture into networks was to answer a question on dynamic ports in
SQL Server. I was pointed to an article on SQLJunkies that pointed out that SQL
Server will try and use the last configured port and if it is used it will use
another one.
This is obviously not a good scenario if your clients have stored the port
number and is only half the story. Let me start from the beginning.
A SQL Server instance has a number of network protocols it supports ( a few
got canned for SQL 2005). One or these is TCP/IP. A service uses
TCP/IP by listening on a port, a webserver for instance by default listens
on port 80 and 443 (SSL). For SQL Server each instance needs to listen on a
different port so it knows which instance should proces which requests. To
enable this, the default instance (unnamed instance) by default listens on
a fixed port 1433 and named instances are configured for a "dyanmic port".
So what does a dynamic port mean. What this means is that SQL Server will
pick a port dynamically when the service is started usually something like
53394. If that port is in use it will try another one. This will continue until
it finds one that is not being used (Only one service can use the same port). On
subsequent restarts of the service it will try and use the same port it used
before. This should work, but if another service has been installed or is runnig
now that didn't before, the port might be being used. In which case it tries to
find an unused port. This is what the article I was pointed to refers.
What is not clear is that this only applies to dynamic port allocation. If
you are using fixed ports then this won't happen. If SQL can't use the
fixed port it will error and won't start.
So is this important. Well you might think that an application has no chance
of working if the port keeps changing because the client application won't know
what port to send requests to. Well this is where the Browser Service comes in
it translates an instance name into a port number. It also provides the
discovery service so applications can find out what instances exist on the
server (this was a big change from SQL 2000 when the sqlServer.exe provided this
information and is how the slammer worm spread).
However whilst the browser service can provide the dynamic port, in most
network environments the ports firewalls only have a few ports open and also
some clients (java ones for a start) hard code the port number in the the
connection sting.
So in a production environment you should always fix the port number for all
instances not just the default instance. How do you do that, well I'll leave
that to another post.
-