Editorials

What Does the SQL Server Browser Service Do?

One other service you will find related to SQL Server in your windows services is the SQL Browser Service. Like the name says, it provides resolution when connecting to SQL Server instances. However, the resolution is local. It does not interrogate other SQL Server Instance on the network, as I originally thought. Where did this service come from, and what value does it provide?

SQL Server 2000 added the capability of installing more than one instance of SQL Server on the same machine. This was a huge benefit allowing the resources of a single server to be more fully utilized, while retaining a specific separation of service resources and security. This was performed by naming the instances of SQL Server installed on a single machine. In order to enable different instances to be identified on the network, each instance had its own TCP-IP port number or named pipe. Clients would connect to the desired network definition, and thus connect with the specific SQL Server instance.

This kind of installation had some issues. An instance could be configured to run on a specific port. However, there is nothing keeping another service from acquiring the desired port first. In this case, the SQL Server instance cannot be found. Some servers have multiple network cards. The ports can be dynamically assigned. Let’s say you do configure SQL Server to use a specific port for an instance. Now your client needs to know that port in order to connect to you database engine. If the port changes, so also, must all of your clients.

The SQL Server Browser service is a connection resolution service. It always runs on port 1434. Now, you client simply contacts the server using UDP on port 1434. The SQL Server Browser service resolves the requested instance, and returns the TCP-IP Port, or named pipe specific for the desired instance to the client. The client now connects directly to the named instance using the resolved connection information.

You can turn of the SQL Server Browser service if your computer only has one instance of SQL Server installed, and uses the default port of 1433. If you want to manage all of your ports using the SQL Server Configuration Tool, you can turn of the Browser Service. However, as stated above, you are now responsible for managing all of the server and client port or named pipe configurations.

For me, unless it is my development machine, I keep the Browser service running. It does a lot of work on our behalf, and uses few resources. For more information about the browser service, there is a great write-up on MSDN at https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail