How SQL Server Browser Service Works

Jul 29, 2014 / By Prabhakaran Bhaskaran

Tags:

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:
 
ExecutiveWorkflow

 

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

 DefaultvsNamed

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:

image003

 

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.

 image004

 

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.

image005
 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.

 image006

 

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:

http://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

image007
References:

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

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

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

Thanks for reading!

 

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>