Tuesday, February 2, 2010

SQL Browser Service

So you have just installed SQL Server 2005/2008 Express. Everything looks good. You then opened SQL Server Configuration Manager. You then looked at the list of SQL Server services installed. You saw SQL Server (SQLEXPESS) service. You quickly thought, “That must be the database engine service. It’s up and running, cool.”

Then you also noticed SQL Browser service. Then you thought to yourself, “Hmm… what’s that? Should that service be running?”.

So, What is SQL Browser Service?

SQL Browser service was introduced in SQL Server 2005. In short, it is a service that provides information about SQL Server instances available on that particular SQL Server to clients. In addition, it also listen to client requests coming to SQL Server. For more information, you might want to read the following reference from SQL Server Book Online: http://msdn.microsoft.com/en-us/library/ms181087.aspx.

Do We Need to Have SQL Browser Service Running?

Well it depends. If you have a named instance of SQL Server (by default, SQL Server 2005/2008 Express is installed as a named instance), and the SQL Browser Service is not running, you might run into some problem connecting to the named instance remotely. For the sake of illustration, let say that you have installed SQL Server 2005 Express, as a named instance of SQLEXPRESS on one of your server named FOOBAR. You then open SQL Server Management Studio on another workstation, and you try to connect to the SQLEXPRESS instance that you’ve just installed. When you are presented with the Connect to Server window, you typed in FOOBAR/SQLEXPRESS. Now if you don’t have SQL Browser Service running, you will most likely get the following error:

Cannot connect to FOOBAR\SQLEXPRESS Additional information:A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

If you have your SQL Browser Service running, you will not get this error. In order to start SQL Browser service, you can do it through the SQL Server Configuration Manager.

Some Additional Notes

You can actually connect to your name instance SQL Server remotely without having to start running SQL Browser server. To do this, you need to specify the port number that the named instance listen to. You can get the port number from SQL Server Configuration Manager. If you are trying to connect using SQL Server Management Studio, to connect to a named instance on a specific port number, you need to use the following syntax on the server name: [Server Name]/[Instance Name],[Port number]. For illustration, if you have FOOBAR as your server name, SQLEXPRESS as your instance name and 1234 as the named instance port, you would type in the following on the server field: FOOBAR/SQLEXPRESS,1234 (Notes: Remember to use comma instead of colon).

You don’t need to have SQL Browser service running if you are connecting using Shared Memory connection (locally).