A Look at the SQL Server Browser Service

Let's say you're starting off in a new environment (new job, department, client, whatever) and after being shown your workspace and meeting with your supervisor, you sit down to start figuring out their databases.  You send an email requesting the name of the development database, and the reply you get is as follows:

DevDB1\Blah, 1436  (It's a named instance)

The backslash makes the fact that it's a named instance pretty evident, and you know that named instances all get their own ports, so you think there's not much to see here.  You fire up SSMS and connect to DevDB1\Blah and can't connect.  After some head scratching, you find that you can only connect when you specify DevDB1\Blah, 1436.

"What an awful instance name!" you think to yourself, and thoughts of creating instance naming standards pop up on your internal to-do list.  While doing some research a little later you realize that spaces and special characters aren't allowed in instance names.  Logging in again and running SELECT @@SERVERNAME confirms that the instance name is indeed DevDB1\Blah.  Some more head scratching reveals that the ", 1436" part specified the port to connect to SQL Server on, and that was necessary because your machine can't connect to the SQL Server Browser Service.

What's it do?

While commonly thought of as "one of those other SQL Server services", life without the SQL Server Browser Service would be a bit more of a hassle. Its main purpose is to aid in the detection of SQL Server instances and provide information necessary for connecting to them.  It listens on UDP port 1434 and returns port numbers and version information for requested instances on a machine.  Its precursor originated in SQL 2000 as part of the SQL Server service.  In SQL Server 2005 it was split into its own separate service.

Each instance of SQL Server is assigned its own TCP network port for client connections.  The default instance listens for connections on TCP port 1433 unless configured to use a different static port.  Named instances can also be configured to use a static port, but by default are assigned dynamic ports that can change each time the service is restarted.  This dynamic port allocation is not allowed on the default instance.

When a user connects to the default instance, the connection is passed to TCP 1433 automatically.  When connecting to a named instance and the port number is unknown, the first connection made is to the Browser Service at UDP 1434, which then returns the proper port number on which to initiate the TCP connection.  If the Browser Service isn't running or is otherwise inaccessible, the connection will fail unless the port number is specified in the connection string.  Specifying a port number means the browser service is not needed, so it is bypassed entirely.

The Proof is in the Packets

I decided to have some fun and see what's really happening for myself.  To do this, I used 2 different tools:

• PortQry is a free download from Microsoft that does exactly as its name describes.  It allows you to query a specific port on a specific machine and see what it returns.
• Wireshark is a free network protocol analyzer that allows you to listen for any and all packets received by your network card(s).  If you're interested in really knowing what's going over the wire, this is the tool for you!

I'm sure you could arrive at similar results with other tools that may or may not be free, but these are what I've used for a while and am quite happy with.

My first trick is to query the browser service to return information about all instances on a box.  For this, I'm using PortQry.  All you need to do to get this information is query UDP port 1434 on a machine running SQL Server.  The syntax to do that is as follows:

PortQry.exe -n <Machine Name> -e 1434 -p UDP

And you should see a response that looks something like this. (All screenshots can be enlarged by clicking on them.)

As you can see when I query my test machine, Bloomingbank, it returns info about the 2 instances of SQL Server on that machine.  One is the default instance (always named "MSSQLSERVER") which is available at TCP 1433, and the other is named "COONLEY", available at TCP 1056.

That's only part of what the SQL Server Browser Service does though.  It also helps facilitate individual connections to instances when necessary.  This behavior can be observed with Wireshark, which can return information about any network traffic that's taking place at all.  For this test, I connected to both default and named instances of SQL server with varying combinations of browser service status and port specifications.  I used the SQLCMD client instead of SQL Server Management Studio because SSMS makes lots of queries when connecting to a server in order to populate the object explorer.  SQLCMD is much simpler and only does what you tell it.

In my first test, I'm going to see how the SQL Server Browser Service performs in a normal usage situation, simply logging into a named instance.  I have Wireshark fired up and listening to all communications with my database, I open a command prompt and run SQLCMD against the named instance and see what happens.

After creating the connection with SQLCMD on the left, Wireshark (on the right) picked up 2 packets using the UDP Protocol, which are circled.  These are the request to the browser service and its response.  After that, TCP communication begins on the port that the service specified.  Looking at the highlighted section in the lower part of the Wireshark screenshot, you can see the response from the browser service containing the server name, instance name, version, and port to connect on.

So what happens if the browser service isn't available?  In the next case I'll run the exact same SQLCMD syntax except now the SQL Server Browser Service has been disabled.  Disabling the service is rather easy and can be done from the SQL Server Configuration Manager.  Wireshark shows the following:

Now we see repeated UDP calls to the browser service, but no replies.  After 5 tries and no response, the client gives up and the connection attempt fails.  The SQL Server instance is up and running just fine, but since the browser service isn't available to tell the client how to connect, it will never succeed.

It's important to note that the SQL Server Browser Service is not required for connections to a named instance.  If you know the port that the named instance is listening on ahead of time, there's no need to connect to the service at all.  For this next test I'll keep the browser service disabled and try to connect using SQLCMD, but this time specifying the port in my connection string.

This time we observe that no UDP packets are transmitted at all, meaning the browser service was never queried.  Once the addresses are resolved, the TCP communication with the instance begins immediately. Trying to make the same connection with the browser service enabled would yield the same result.  If the client is told what port to connect on, it sees no need to query the browser service for port information.

After all this talk about named instances, let's switch gears and talk about the default instance a little bit.  I'll perform the same test using Wireshark while connecting to the default instance with the browser service re-enabled:

Again there's no UDP packets, meaning the browser service was not utilized.  This makes sense because we're connecting to a default instance on its default port, which is TCP 1433.  Since the client is correctly assuming the port, it doesn't see the need to waste the time and effort of querying the browser service.

But what if the client assumes the port incorrectly?  As I mentioned above, it is possible to configure the default instance to use a different static port using the SQL Server Configuration Manager.  I changed my default instance to use TCP 4858 instead of the default port.  I'll run the same query as above:

You'll notice the result is a lot more colorful now. The client always assumes that the default instance can be found on the default port. When this is not the case it will try to connect multiple times but will never succeed. In the trials I ran, SQLCMD gave up and timed out after about 8 seconds of trying to connect. After some digging around, I was able to find a Microsoft blog post confirming that their client network libraries do not attempt to dynamically discover a TCP port for the default instance. They suggest several solutions for situations where the default instance is not on the default port, the simplest of which is just to specify the correct port number in your connection string.

The Chart!

If there's something to be learned, I'll probably find a way to make a chart out of it! In this case I've taken all of the above results and put together charts showing client behavior for both the default instance and named instances.

Further Reading

SQL Server Resolution Protocol Specification – a short (~30 pages) yet interesting read about the protocol the browser service uses for communication.  Microsoft was kind enough to release it as a completely open standard.