What's the best way to test SQL Server connect

Question

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!

Answer 1

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....

Answer 2

I have had a difficulty with the EF when the connection the server is stopped or paused, and I raised the same question. So for completeness to the above answers here is the code.

/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
    }
}

Answer 3

Execute SELECT 1 and check if ExecuteScalar returns 1.

Answer 4

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.