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

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.

Is there a way to simply "ping" a SQL Server from C# one with minimal code and sql operational requirements?

Answer 1

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 2

Execute SELECT 1 and check if ExecuteScalar returns 1.

Answer 3

See the following project on GitHub: https://github.com/ghuntley/csharp-mssql-connectivity-tester

try
{
    Console.WriteLine("Connecting to: {0}", AppConfig.ConnectionString);
    using (var connection = new SqlConnection(AppConfig.ConnectionString))
    {
        var query = "select 1";
        Console.WriteLine("Executing: {0}", query);

        var command = new SqlCommand(query, connection);

        connection.Open();
        Console.WriteLine("SQL Connection successful.");

        command.ExecuteScalar();
        Console.WriteLine("SQL Query execution successful.");
    }
}
catch (Exception ex)
{
    Console.WriteLine("Failure: {0}", ex.Message);
}

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.