I made a mistake the other day. I’ll admit it. I was wrong. Now I know all of you are shocked, but it does happen on rare occasions. So what was I wrong about you might ask? One of the dev groups I support has been referring to their instances as ServerName/InstanceName:PortNumber and it was driving me bananas. I mean first of all using the InstanceName and PortNumber is redundant, and second generally the best practice is to use just the InstanceName in case the port changes. Right? Turns out in this particular case I was wrong. Not about the redundant but about using the InstanceName instead of the PortNumber.

TL;DR at the bottom.

Let’s start with a bit of background. I’m going to assume that everyone knows the default port number for the default instance is 1433. I’m further going to assume that everyone has the basic logic skills to understand that named instances cannot use that same port number. By default a named instance uses a dynamic port. This port is generated the first time the instance is started and can but does not always (in fact it’s pretty rare) change each time the instance is re-started. That’s important to remember for later. A dynamic port can but does not always change each time the instance is re-started. Now I said, by default a named instance uses a dynamic port and by default a default instance uses port 1433. In both of these cases, just as you would expect, this is configurable. Using the configuration manager you can easily modify the port being used at an instance or even IP level.

Pro Tip: This means I could have three IP address pointing to the same instance, one of them port 1433, one of them a dynamic port, and the third a static port number other than 1433. Extending this somewhat I can create an instance named ServerA\InstanceName, then add two additional IP addresses aliased ServerB and ServerC. The original IP address ServerA I leave as a dynamic port, the IP address for ServerB I make a static port 54123 and the last IP address I point to port 1433. I can now call that instance ServerA\InstanceName, ServerB\InstanceName, ServerC\InstanceName, and, ServerC with no InstanceName. So it now looks like we have a default instance on ServerC even though in reality it’s a named instance. Personally I find this ability to spoof a default instance really cool and if you want to do some additional reading on it try searching for DNS aliasing.

So since a named instance is probably going to be dynamic and even if it wasn’t we typically use the instance name to connect anyway how does the system know what port number to use? This is where the browser service comes in. The first thing that SQL does is ask the browser service (using port 1434) what port the named instance is using so that the connection can be made using that port. (If no port number or instance name is used it is assumed to be the default instance on port 1433.) So when you use the instance name it’s actually a two step process. Connect to the browser to get the port number, then connect to the SQL Server. Please don’t think this means that it’s faster to use the port number. We are talking a very small fraction of a second, once, for the entire connection. No one will notice.

Typically we use the name of the instance instead of the port number just in case the port number changes after a reboot. Not to mention the fact that a name is much easier for a person to understand than a number. It’s pretty easy to understand what ServerA\TEST is going to be used for, ServerA:52345 not so much.

This is a long way to go to get to my mistake right? Necessary background, sorry.

So when would it be best to use the port number rather than the instance name? When there is a firewall involved. Ports on a firewall have to be specifically opened (at least the ones I’ve dealt with do, I’m not an expert). That means that if the port changes dynamically the new port is going to be blocked by the firewall. This is not good. Particularly in production. So my first piece of advice to you is to make sure that your port is set to static if you are working behind a firewall. Next, you will remember that I said that calling an instance by name is a two step process, right? Two steps, two ports. That means that two holes have to be opened up in the firewall. Possibly not a huge issue, except that port 1434 is pretty well known.

Start TL;DR here:

So now we come to it (finally). These users were behind a firewall. They always referenced the port number in order to avoid having to open port 1434 in the firewall. They included the name of the instance as well because that makes it much easier to understand what instance they are talking about (particularly with our crazy server name naming conventions).

In case you are interested I apologized (in my head) for all of the mean things I said about them (in my head).

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication Tagged: instance settings, microsoft sql server, port 1433, tcp port