We ran into a situation when SQL browser service was not running on the server, we followed the following steps to reach a state of getting browser service to run.
Environment Windows Server 2003 SQL Express 2005 Service Pack: SP2
· We followed the KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
· SQL browser service was already set to “automatic”.
· When we try to start the service ,we got this error :
TITLE: Surface Area Configuration ------------------------------
The service did not respond to the start or control request in a timely fashion, you need administrator privileges to be able to start/stop this service. (SQLSAC)
· The application event logs had the following errors :
The configuration of the SQL instance MSSQLServer is not valid. The configuration of the AdminConnection\TCP protocol in the SQL instance <instance name> is not valid.
The SQLBrowser service was unable to establish SQL instance and connectivity discovery. The SQLBrowser is enabling SQL instance and connectivity discovery support
To get more information on reason for service failure, you can start the SQL Browser as a console application
· When we try starting SQL browser as a console application we got the following errors :
C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c SQLBrowser: starting up in console mode SQLBrowser: starting up SSRP redirection service SQLBrowser is successfully listening on 0.0.0.0 SQLBrowser: failed starting SSRP redirection services -- shutting down.
What is SSRP Redirection? SQL Server Resolution Protocol (SSRP) was developed to listen on port 1434, beginning with SQL Server 2000. This was how we managed to have multiple SQL Server instances on the same machine. The SSRP redirector as the name suggests “redirected” the client requests to the appropriate instance based on the Instance Name or Port # or Named Pipe.
· We found a key under SQL server registry hive:
· This is from an older default installation of SQL server 2000. This should not exist anymore since SQL Browser is a replacement for the SSRP mechanism.
· If above key exists, SQL Browser during startup tries to connect to this instance (non-existent) and hence fails
· So we renamed above key it to CurrentVersion_Old and tried to restart the browser services, this time we were able to do so.
Note: - 1. Please check for the existence of this key under the wow6432node key as well. HKLM\SOFTWARE\WOW6432\Microsoft\MSSQLSERVER
2. Please make sure that you do not have any SQL Server 2000 instances running anymore before making the registry change.
Amrutha Varshini SE, Microsoft SQL Server
Sudarshan Narasimhan TL, Microsoft SQL Server
1 How do you know it was not running
2 Where is the option to request that it runs
3 How to verify from the network it can be reached
Answers below :-
1.How do you know it was not running ?
Ans: Look up services.msc or SQL Configuration Manager and the status will show up as "Stopped".
2. Where is the option to request that it runs ?
Ans: Again using above methods, you can set the service to "Automatic" so that it always run on server boot up.
3.How to verify from the network it can be reached ?
Ans: Try to connect to your SQL Server using "SERVERNAME\InstanceName" from a client machine using sqlcmd or Management Studio, it might give you "SQL Server does not exist or access denied".
Even simpler, do a UDL test query and you will get the same if the SQL Browser service is not listening. To workaround this you can connect directly using IP Address or named pipe (these don't require browser service to be running)
Is sqlbrowser related to internet browser sudi?
How fast can it run?
Should I have one running per instance?
>> Is sqlbrowser related to internet browser sudi?
SQLBrowser is not related to Internet browser.
>>How fast can it run?
Not clear with this question.
>>Should I have one running per instance?
One service would be installed for one machine. this would take care to answer all incoming request.
We found a key under SQL server registry hive:
could you please tell me which key??? I don't see any related key in this node.
hi sudharshan i have a peculiar problem my Sql service when started goes back to stopped status
im running the sql i think 2000 version.how can i get this to work again..
pls also copy mail to email@example.com
Could you please check SQL ERRORLOGs and see if there is any error message causing this problem?
I would also suggest you to post your question in MSDN forum where many peoples can provide help.
how to know from sql server whether the service is running or not like SMTP, sql server agent?