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.
Is there a way to simply "ping" a SQL Server from C# one with minimal code and sql operational requirements?
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;
}
}
}
Execute SELECT 1
and check if ExecuteScalar returns 1.