How SQL Server browser service Works

4 min read

Jul 29, 2014

Some of you may wonder the role SQL browser service plays in the SQL Server instance. In this blog post, I’ll provide an overview of the how SQL Server browser plays crucial role in connectivity and understand the internals of it by capturing the network monitor output during the connectivity with different scenario.

Here is an executive summary of the connectivity flow:

Here is another diagram to explain the SQL Server connectivity status for Named & Default instance under various scenarios:

Network Monitor output for connectivity to Named instance when SQL Browser is running:

In the diagram below, we can see that an UDP request over 1434 was sent from a local machine (client) to SQL Server machine (server) and response came from server 1434 port over UDP to client port with list of instances and the port in which it is listening:

Network Monitor output for connectivity to Named instance when SQL Browser is stopped/disabled:

We can see that client sends 5 requests which ended up with no response from UDP 1434 of server. so connectivity will never be established to the named instance.

Network Monitor output for connectivity to Named instance with port number specified in connection string & SQL Browser is stopped/disabled:

There is no call made to the server’s 1434 port over UDP instead connection is directly made to the TCP port specified in the connection string.

Network Monitor output for connectivity to Default instance when SQL Browser running:

We can see that no calls were made to server’s 1434 port over UDP in which SQL Server browser is listening.

Network Monitor output for connectivity to Default instance which is configured to listen on different port other than default 1433 when SQL Browser running:

We can see that connectivity failed after multiple attempts because client assumes that default instance of SQL Server always listens on TCP port 1433.

You can refer the blog below to see some workarounds to handle this situation here:

https://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/03/03/running-sql-server-default-instance-on-a-non-default-or-non-standard-tcp-port-tips-for-making-application-connectivity-work.aspx

References:

SQL Server Browser Service – https://msdn.microsoft.com/en-us/library/ms181087.aspx

Ports used by SQL Server and Browser Service – https://msdn.microsoft.com/en-us/library/ms175483.aspx

SQL Server Resolution Protocol Specification – https://msdn.microsoft.com/en-us/library/cc219703(v=prot.10).aspx

Thanks for reading!