What's the best way to test SQL Server connection programmatically?

views:

499

answers:

+7 Q:

What's the best way to test SQL Server connection programmatically?

I need to develop a single routine that will be fired each 5 minutes to check if a list of SQL Servers (10 to 12) are up and running.

I can try to obtain a simple query in each one of the servers but this means that I have to create a table, view or stored procedure in every server, even if I use any already made SP I need to have a registered user in each server too. The servers are not in the same physical location so having those requirements would be a complex task. Is there a way to simply "ping" from C# one SQL Server?

Thanks in advance!

+4 A:

Wouldn't establishing a connection to the database do this for you? If the database isn't up you won't be able to establish a connection.

confusedGeek 2010-03-13 21:18:00

Indeed, simply use ADO net to Connect - if there is no response within the timeout period then the database is not available. You don't need to issue a query to ascertain that.

Dan Diplo 2010-03-13 21:21:03

To check with ADO.NET I need a user, I just want to check if the service is up and running, no problem about if a database is up. I need something like telnet an SMTP server. No need to have a user to obtain a response.

backslash17 2010-03-13 21:26:13

@backslash17: The "Login failed for user..." response should be enough to confirm that 1) the machine is up and 2) the service is running. If you get a connection timeout, then the service isn't running/working.

Rory 2010-03-13 21:34:36

@Rory: Good point! Is just matter of checking the error in a try/catch block. Thanks!

backslash17 2010-03-13 21:40:26

@backslash17 Check that "Login failed for user" has separate error code or something that helps you to determine it. It is very bad practice to differ errors by exception message.

Andrew Bezzub 2010-03-13 22:02:47

+1 A:

Execute SELECT 1 and check if ExecuteScalar returns 1.

Andrew Bezzub 2010-03-13 21:22:17

That's very good, no need to have any object created in a database, but I need to have a database and a user to make a query. I just want to know if the service is up in the MSSQL port. Anyway, you're solving almost 75% of the problem. This would be an option.

backslash17 2010-03-13 21:32:41

You have master db anyway :) The cleanest way to check if SQL Server is running is connect to it. To connect you need db and login anyway. All other solutions (like pinging SQL Server port) won't guarantee that SQL Server is running properly and anyone could connect to it.

Andrew Bezzub 2010-03-13 21:55:47

+1 A:

Look for an open listener on port 1433 (the default port). If you get any response after creating a tcp connection there, the server's probably up.

Joel Coehoorn 2010-03-13 21:46:11

This won't give 100% answer.

Andrew Bezzub 2010-03-13 21:53:54

@Joel Coehorn: For me is a good answer! Any example on that?

backslash17 2010-03-13 21:58:37

For what Joel Coehorn suggested, have you already tried tcping [http://www.elifulkerson.com/projects/tcping.php]. It is a standalone executable which allows you to ping every specified time interval. It is not in C# though.Also..I am not sure If this would work If the target machine has firewall..hmmm..

ydobonmai 2010-03-14 09:01:55

For what Joel Coehorn suggested, have you already tried the utility named tcping [elifulkerson.com/projects/tcping.php]. I know this is something you are not doing programmatically. It is a standalone executable which allows you to ping every specified time interval. It is not in C# though. Also..I am not sure If this would work If the target machine has firewall..hmmm..

[I am kinda new to this site and mistakenly added this as a comment, now added this as an answer. Let me know If this can be done here as I have duplicate comments (as comment and as an answer) here. I can not delete comments here.]

ydobonmai 2010-03-14 09:07:36

Why not just connect to telnet session on the sql server port. If it connects, sql server is up and happy, if not, you're out of luck.

This other StackOverflow post might be a good place to start with that.

EDIT: OK, now I've fully read the other posts this isn't exactly the best solution... Still, if you just want to ping the port....

pms1969 2010-03-14 10:24:09

ansaurus

tags:

views:

answers:

What's the best way to test SQL Server connection programmatically?

related questions