SQL Browser service explaination

SQL Server browser service:

-------------------------------------------

The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

·         Browsing a list of available servers
·         Connecting to the correct server instance
·         Connecting to dedicated administrator connection (DAC) endpoints
 

TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express.

If specific port is assigned client can connect with specifying por tno along with instance name but for dynamic port client won’t be aware of port no of instance. Here sql browser comes in picture to access the port no and instance name installed on server.

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

However, if the SQL Server Browser service is not running, the following connections do not work:

·         Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

·       Any component that generates or passes server\instance information that could later be used by other components to reconnect.

·         Connecting to a named instance without providing the port number or pipe.

·         DAC to a named instance or the default instance if not using TCP/IP port 1433.

If you are using SQL Server in a client-server scenario (for example, when your application is accessing SQL Server across a network), if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:

·         You must update and maintain client application code to ensure it is connecting to the proper port.

·         The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.