What is SQL Server Browser Service
It is a windows service which helps the clients by providing information about SQL Server instances running in the machine. For example, if a client knows only SQL instance name say server1\namedInstance2, the SQL Server browser helps the client to find the exact port number of named instance. Port number is needed to connect to the to the SQL Server Engine if it is using TCP to connect and uses non standard 1433 port.
How SQL Server Browser works?
SQL Server Browser works over UDP port 1434. It doesn't seems we can change that UDP port as it defeats the purpose of endpoint discovery. In short clients ask information about SQL Instance and browser service answers it.
How SQL Server Browser looks like from Wireshark?
SQL Server browser activities can be seen through wire, if we have right network tools installed. Wireshark is the best tool to begin with and its widely popular for these type of exploring. It is free to use. There is already a great article which shows how it works over the wire. Link is below
It is assumed that the application to SQL Server communication would be via TCP as normally as best practice applications and databases are placed in different machines. If the named pipes or shared memory is used, the troubleshooting will vary.
Wireshark to see SQL Server Browser failure
Normally on development environment the 1434 port would be open and SQL Server Browser running in SQL Server machine. If developers code based on that assumption, it may fail in higher environments. Most of the developers know what might have happened. But some may not and ends up in complete darkness. The symptom of this issue will be connection failure from application to SQL Server database.
This post is mainly to demonstrate how to check network failures on SQL Server Browser operations. The prerequisite is the availability of Wireshark in the machine where application runs and basic knowledge how to run the same. The above link has some screenshots which would help to run Wireshark for UDP connections.
The failure scenario can be confirmed by below steps.
- Make sure the Wireshark is capturing UDP 1434 traffic in the machine where application runs.
- Reproduce the scenario. Better use SSMS or any test client tools to establish a connection than full application. Again if allowed to use SSMS or other tools in higher environments.
- If the UDP requests are made one after another like retry, there is an issue. Refer the screenshot below from the frames starting 3. The initial 2 frames shows the working scenario.
- The UDP will fail, if any of the below is true.
- The firewall rules blocks the port
- The SQL Server Browser is not running in the SQL Server machine.
If the everything is in working state and the client application is not using specific TCP port to connect to SQL Server Instance, the client will get UDP response from server with the port number. Else it retries 30 times and times out.
We can easily filter the Wireshark logs if we already knows what is the SQL Server machine IP Address. The source would be the SQL Server machine IP and the destination application machine IP.
Caching the port number by client applications
The clients may cache the TCP port number what they got from UDP response. In such scenario based on the nature of the client, we may need to restart or wait for the cache expiry.
Similarly The SQL Server Browser might be stopped or communication is blocked in any other ways, during the normal working. If it happens, the old clients works but new clients don't. It is better to restart the clients to ensure there is connectivity.
References
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-2017https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d234c318-dead-4aab-bd2d-7c0cd8f4261a/sql-browser-and-1434-in-sql-server-2016?forum=sqldatabaseengine
https://www.mssqltips.com/sqlservertip/2436/what-does-my-sql-server-data-look-like-over-the-wire/
No comments:
Post a Comment