A new service, SQL Server Browser, was introduced with SQL Server 2005 and is also used with SQL Server 2008.
The SQL Server Browser service introduced in SQL Server 2005, runs as a Windows service and it is an extension of SQL Server Resolution Protocol (SSRP) of SQL Server 2000.
Like SSRP it plays an important role while working with more than one SQL Server instance installed on the same machine. The basic purpose of the SQL Server Browser [gs service] is to provide instance and port information to incoming connection requests.
To configure a better and safe access mechanism for SQL Server, a [gs DBA] should have proper understanding of the SQL Server Browser service.
Below I will discuss the access mechanism with reference to the TCP/IP [gs protocol].
How to access SQL Server Browser service
SQL Server Browser service can be configured during installation or after installation it can be accessed in number of ways. Here are couple of common ways to access the SQL Server Browser service.
It should be noted that SAC just provides access to start/stop or to change the startup type for the SQL Server Browser service.
For management of all other [gs parameter]s like ports, paths you have to use SQL Server Configuration Manager. Also SQL Server Browser service can be started by using command prompt.
What if SQL Server Browser service is stopped?
The SQL Server Browser service is installed with installation of SQL Server 2005/2008. It also serves earlier versions installed on the same machine if there any. The SQL Server Browser service has different impact in different situations, such as:
Best practices for using SQL Server browser service
The way you use SQL Server Browser service affects the access to your SQL Server instances, hence the security of installed instances.
At one end, the most security conscious approach may be to use customized static ports for your instances and access SQL Server with fully qualified connection parameters. It would include IP + InstanceName + Port. In this configuration your instance would not be exposed to the network also more parameters are required in the connection string.
It would be a secure configuration compared to having the SQL Server Browser service running and serving the incoming connection requests with instance and port information. If you have no problem in using fully customized connection strings (IP + Name + Port), then you can stop the browser service permanently and access the instance through a fully qualified connection string.
In another type of configuration, if the instance is configured to use dynamic ports each time, then SQL Server Browser service should be running, otherwise there would be no way to keep track of the dynamic ports that are assigned.
If you have just a default instance installed (with default port) on your machine then SQL Server Browser service may be stopped to avoid overhead.
As part of best practices, always run SQL Server Browser service with a minimum privileged account. According to BOL any Windows user having the following rights would be capable to run the SQL Server Browser service.
You can change the default account of the browser service (local system), to any other user having the above mentioned rights on the machine.
Limitations and further considerations
Testing the configurations
I have a named instance with the following customized static port as shown in TCP/IP properties of instance in SQL Server Configuration Manager.
If SQL Server Browser service is not running then start it through the command prompt or any of the other ways mentioned above.
When the [gs browser] service is running, I can access the instance without a specifying the port. I can successfully login with the following connection parameters (IP + InstanceName).
Now stop the SQL Server Browser service with net stop sqlbrowser command in the command prompt or one of the other ways that were mentioned above. Also exit SSMS and reopen it.
Try to login again with the same parameters as before. This time you will get a message like the following.
The reason is that the browser service is not running and the customized static port is also missing in the connection parameters.
To login successfully we have to provide the customized port number in the following way. The server name field would need IP\InstanceName,PortNumber.
Hopefully this has given you enough information to understand how the browser service runs and when and when you do not need to have this service running.
Next Steps
Before making any changes to the SQL Server Browser service make sure that connection strings in existing code are changed to meet the changed configurations, otherwise you may have some connection issues.
SOURCE | LINK | LANGUAGE | ENGLISH |
Overview of the SQL Server Browser service (SQL Server 2005/2008) – HeelpBook – Visit http://www.heelpbook.net OR http://mobile.heelpbook.net on your tablet! – 29/01/2013 – 14:26 @HeelpBook #HeelpBook