The Microsoft SQL Browser Service is a service that runs on a machine that has Microsoft SQL Server installed, and provides information to clients about the SQL server instances installed on the PC.
When you are connecting an application to a SQL server, generally the software has the capabilities to send a broadcast query out onto the network, and detect what SQL servers are available. This is generally presented to the end user with a drop down box listing all the SQL services, and the instance names. This broadcast query doesn’t communicate to the actual SQL Server directly, but it communicates with the SQL Browser service. This means that if the SQL Browser service is stopped, no client software will be able to automatically determine that there is a SQL server running.
With Microsoft SQL Server you can install a number of independent SQL Servers on a machine, and differentiate them with “instance names”. If you only want one SQL server installed, you can either set up an instance name, or use no instance name, which will be known as the “default instance”. With SQL 2008 R2, there is a limit of 50 instances per computer.
Each instance is completely separated, and databases set up on the individual instances. This can allow you to separate payroll database with tighter security, run multiple versions of SQL server, have a production and development SQL server, etc.
Since there can me many SQL instances running on the one computer, there needs to be a way to separate them at a network level. Each instance will run on its own unique TCP/IP port number. This is where the SQL Browser service comes into play. The SQL Browser service is queried before each initial SQL server connection, and can determine what instance name you are trying to connect to. It passes back to the client the port number that the specific SQL instance is listening on. If the SQL Browser service is stopped, you will no longer be able to connect to any SQL server instances (apart from perhaps the default instance if your lucky).
The SQL Browser service listens for incoming SQL queries on UDP port 1434. You must make sure this port, and also the TCP port that all your SQL instances are using, are allowed through any firewalls that might be in place on the machine running the SQL server.
Windows 2008 Server has a tendency to block these ports by default, so it can cause a few issues if your not aware of it.
Even if the SQL Browser service is stopped, it doesn’t prevent users from connecting to it, as long as you know the server name, instance name, and port number.
To connect directly to a SQL server instance running on a specific port, you would use the following format:
SERVERNAME\INSTANCENAME,PORTNUMBER
eg:
SQLSVR3\DEVELOPMENT,15032
This will bypass the SQL Browser service query, and connect directly to the SQL server called “SQLSVR3” with an instance name of “DEVELOPMENT” which is running on TCP port 15032.
For more information, check out the Microsoft article on the subject at http://msdn.microsoft.com/en-us/library/ms181087.aspx
If you need information on setting up SQL to listen on TCP instead of just Named Pipes, please check out my guide on Enabling TCP/IP in Microsoft SQL Server 2005.
