Performance of application with SQL Browser Service Off

Bert2005
Bert2005 used Ask the Experts™
on
Experts:

In reading about the SQL Brower Service, it seems to say that if it is off and the instance of the application is not connecting via TCP/IP, the client app will not work.

Is this true, and if is, is it possible for some type of variation of the above to allow the application to connect to SQL but certain parts of the application work less efficiently, e.g. more slowly? This is SQL 2005 Express on an SBS 2008 Standard server.

Thanks.

Bert
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Well, first of all, SQL Browser Service is necessary when you have more than 1 SQL server instances installed on a given server. With SQL Browser Service, you would not able to achieve what you are looking for, ie dynamic resource allocation. What you are really want is Resource Governor. but unfortunately, that is only available  from SQL server 2008 onward.

Author

Commented:
But, if I had two instances, which I do, would SQL Browser Service need to be started?

Thanks for the help.
yes, You should turn it on if you have two or more instance. According to Microsoft doco on  SQL Server Browser Service

Under the section 'Using SQL Server Browser'
If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

However, if the SQL Server Browser service is not running, the following connections do not work:

1.Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).
2. Any component that generates or passes server\instance information that could later be used by other components to reconnect.
3. Connecting to a named instance without providing the port number or pipe.
4. DAC to a named instance or the default instance if not using TCP/IP port 1433.
5. The OLAP redirector service.
6. Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

It is much easier to have it turned on. without, you will really need to configure a lot of things to make sure your application can connect to the right instance.
Just thought of it again, say SQL Browser Service is like virtual directory in IIS. without virtual directory, you can connect to one of the website that hosted on the same IIS box but less user-friendly. like virtual directory, SQL Browser Service just translate your request and pass it to the instance. as a result, save you work.

Author

Commented:
Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial