SQL Browser – How does this Windows service help SQL Server?
In most practical installations in organizations, the database is usually arranged in a distributed manner. In this arrangement, several clients over the network communicate with the server hardware. There could, of course, be more than one physical server hardware too. In the simplest configuration, this will be a single instance of the SQL Server. However, in a general situation, there will be multiple instances of SQL server running.
SQL Server is assigned a port number 1433 on which to listen for connection request and transfer data back and forth. The communication with clients is established through a named pipe that connects to this particular port. Problem arises when there are several instances of SQL server is running simultaneously. You need a mechanism to make sure the communications with clients happen systematically, and data is passed back and forth between the requested instance of the server and the specific client, even though there is only one port that could be used even when multiple instances exist. SQL Browser is the tool that manages the set of communications exchanges that are needed between clients and an instance of the SQL SERVER. This SQL Browser mechanism was introduced with the release of the SQL server 2005. SQL Browser has been available in the following releases namely the SQL Server 2008, 2008 r2 and 2012 releases.
SQL Browser or SQL server browser (the official name) listens on the specified port and provides information about the instances that are in existence within a server. The SQL Browser works as a Window service and is present in the machine all the time. The service is able to browse the list of servers available with id and software version number of each instance. When the request comes the SQL server also connects the client to the right instance of the database. The SQL Browser service helps connect to dedicated administrator connection end point additionally. In the Express version of the databases in 2005 as well as later versions, SQL Browser is not enabled. The configuration manager of the SQL server will help set up the SQL Browser in the server.
The SQL 2008 Browser works the same way across the versions.
- The default server instance uses the port 1433, and a named pipe named as “\sql\query”. Server administrator can change the defaults through the server configuration manager, of course.
- Ports are assigned dynamically to the server instances.
- When the server instance stops, for some reason, and is restarted, this assignment can change.
- Thus, client would not know which port number to use for communicating.
- Usually the SQL Server Browser uses port 1434. Initial request from a client is sent to this port. SQL server, on request, passes on the port number and the named pipe that has been assigned to the particular instance when it was started.
- The client then uses these specific resources to communicate with the right server instance. The SQL Browser service can be started and stopped by the database administrator as needed.
The browser is a necessary tool for real-life situations using multiple database servers with clients distributed on the enterprise network.
If you would like to learn more, check out our page on SQL Server 2012 Training.