Recently one of my application website went down.
I checked all the basic connectivity troubleshooting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state.
Error from the event viewer:
The quick solution is rebooting the SQL browser (Start –>All programs–>Microsoft SQL server 200X–>Configuration tools –>SQL server configuration Manager) without rebooting SQL service.
I searched and found a couple of MS links (KB-2526552 And SQLBrowser Unable to start) but, I did not apply it. I used another way that is also a permanent fix.
Troubleshooting ways and a permanent fix:
For me it’s a named instance and listening a dynamic port and DBAs knows the browser service is mainly for named instance.
From the local machine we can connect the server through SSMS by using server name and server name + port number. But, other than local machine you cannot connect the server by using server name. (You can test that by connecting some other server or better install only SSMS on the application server and try to connect it) so I went to the application server and opened a connection string as expected the data source only has the server name. So We changed it from Datasource “from Data Source=Servername\Instance to Data Source= Servername\Instance,port” Ex: Muthu1\SQL1,5432.
Application team made a standard to always include a port number in the connection string block i.e. FQDN.
A Basic SQL Connectivity checks:
- Check SQL service is running or not and try to connect through SSMS from local and remote
- Check TCP/IP protocol enabled on SQL server configuration manager and find the port number
- Connect using a server+port number from SSMS local and remote
- For firewall block/port not opened you can check through command prompt TELNET server port ex: TELNET server 1433
- Check remote connections are enabled & SQL Browser service is running (For a named instance which is not using FQDN)
- Check you have any alias/DNS name
