Getting to Know and Love the SQL Server Browser Service

A while ago I wrote about some problems I had logging into GP that were caused because the SQL Server browser service was not running.

I'd like to (belatedly) follow up with an explanation of the history behind the SQL Server Browser service, and why you should make sure it is running on a Dynamics GP SQL 2005 server.

Back in the days of SQL Server 7.5, you could only have one "instance" of SQL Server on a machine. That single SQL server listened for connections on port 1433. Life was simple, and all was peaceful. (except for the single instance limitation)

With the release of SQL Server 2000, you could install multiple SQL instances on the same machine, effectively allowing you to host multiple SQL Servers on one physical box. This was great, but there was one problem. Two or more SQL instances couldn't all listen on port 1433. So a new protocol and dynamic port design was developed that would listen on port 1434, and serve as a directory of sorts, telling clients the names and port numbers of SQL instances installed on the machine.

If you've ever had to troubleshoot obscure connectivity issues with SQL 2000, you probably know that it can be frustrating to troubleshoot down to the port level, since you could never assume what port a given SQL instance might be using. And there were also some challenges related to configuring the client communication protocols, causing headaches determining if named pipes or TCP/IP wasn't working properly.

Well, with SQL Server 2005, a new solution was introduced to help make life easier with regard to SQL Server ports and client connectivity. One element of that solution is the SQL Browser service.

The SQL Browser service helps clients identify and connect to SQL instances, and SQL Server Configuration Manager allows you to specify client connection and protocol settings on the SQL Server, rather than having to configure each client PC individually. When used with the SQL Native Client Driver on workstations, this is a nice improvement, reducing installation and administration effort.

Microsoft MSDN has a nice page that briefly covers the background I just described, and provides some additional details on the mechanics and operation of the SQL Browser service.

But, alas, the Achilles Heel of all of this is that the SQL Browser service may not be configured to start automatically when SQL Server is installed. So if you run into obscure problems and spend an hour or two scratching your head wondering why GP won't connect, make sure to add the SQL Browser service to your troubleshooting checklist.