How to Efficently Test Whether an SQL Server Instance is Running in C#

Question

I have an SQL Server (2008 R2) based (C# WinForms) application that predominantly runs on a local machine using a local installation of SQL Server 2008 R2. One problem I have is that if the user does not have a server instance running and tries to execute some commands or perform some operations, the queries are sent off to SQL Server and it takes an age to throw an SqlException telling me the requested instance is not started.

I have read the following question and associated answers, but these solutions are far from ideal. WMI seem very much over-kill and I do not want to have to include extra .dlls in my installation package for the software if it can be avoided.

I have also come accross the SqlDataSourceEnumerator Class documented here

// Retrieve the enumerator instance and then the data.
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();

which dumps the available connection into a DataTable. However, there seems to be inherent problems with returning all the available connections:

"All of the available servers may or may not be listed. The list can vary depending on 
 factors such as timeouts and network traffic. This can cause the list to be different 
 on two consecutive calls." - MSDN.

There has to be a set way of dealing with this problem. Say I have the following SqlConnection string:

Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True;Connection Timeout = 0

what can I use as an efficient (this is crucial) check as to whether the selected instance ('localhost' [the default instance] or 'SomeInstanceName') is running?

Thanks for your time.

If you simply try opening a connection to that instance and it does not exist or is not available you should be able to capture the exception and handle accordingly. — mreyeros
– mreyeros, Commented Feb 16, 2012 at 14:25
Yes but it can take serveral munutes to come back in some curcumstances! Cheers. — MoonKnight
– MoonKnight, Commented Feb 16, 2012 at 14:27
Take a look at the answers to this question. It is asking about Oracle but also applies to SQL server. — M.Babcock
– M.Babcock, Commented Feb 16, 2012 at 14:27
Sure, it can take several minutes to come back, if the server isn't running. You can't make a more efficient connection attempt but you can shorten your connection timeout (say, 5 seconds). — Aaron Bertrand
– Aaron Bertrand, Commented Feb 16, 2012 at 14:30
I thought below you answered "I could be wrong but does the connection timeout not also determine how long it waits from the query to run before timing out?" with "No, that is a command timeout"? I was just going to write: If the Connection Timeout is set to zero the time taken to throw the SqlException telling me there is no running instance is of the order of minutes. For a timeout of ten seconds it is about ten seconds. There must be some correlation here? — MoonKnight
– MoonKnight, Commented Feb 16, 2012 at 14:40

Aaron Bertrand · Accepted Answer · 2012-02-16 14:27:44Z

2

I don't think you need to worry about timeouts or network issues when the server and client are the same machine. Just attempting to connect is about efficient as you're going to get, the crucial part is going to be how long do you let the connection attempt try before you give up (connection timeout). You can shorten that window obviously, but if you make it too short, then the problem doesn't really make sense.

answered Feb 16, 2012 at 14:27

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Coding Flow Over a year ago

I could be wrong but does the connection timeout not also determine how long it waits from the query to run before timing out?

Grant H. · Accepted Answer · 2012-02-16 14:26:09Z

1

You can change the connection timeout to be a shorter period, but essentially, the only way it knows that a server isn't there, is from a timeout.

answered Feb 16, 2012 at 14:26

Grant H.

3,7172 gold badges39 silver badges55 bronze badges

Comments

John Saunders · Accepted Answer · 2012-02-16 14:25:35Z

1

Any technique you use will likely have the exact same timeout issue.

answered Feb 16, 2012 at 14:25

John Saunders

162k26 gold badges252 silver badges403 bronze badges

Comments

grouchy1953 · Accepted Answer · 2015-09-23 17:18:36Z

1

If you know the instance name, such as "MSSQL$InstanceName" you can use the System.ServiceProcess.ServiceController class to get a list of all services on the machine and then loop through looking to see if any ServiceName == MSSQL$InstanceName.

I have found this to be very fast plus you can check to see if it is running and start it if it is not running.

answered Sep 23, 2015 at 17:18

grouchy1953

111 bronze badge

Comments

Alex · Accepted Answer · 2012-02-16 15:34:26Z

0

You could try opening up a simple TCP connection to the standard SQL port and see if it sticks . set the connection timeout to some reasonably low value based on your environment.

see

http://support.microsoft.com/kb/287932

for sql serevr port numbers.

answered Feb 16, 2012 at 15:34

Alex

7581 gold badge5 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to Efficently Test Whether an SQL Server Instance is Running in C#

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related