ADO.NET: Faster way to check if the database server is accessible?

Question

At the moment I am using this code to check if the database is accessible:

public bool IsDatabaseOnline(string con)
{
    bool isConnected = false;
    SQLConnection connect = null;

    try {
        connect = new SQLConnection(con);
        connect.Open();
        isConnected = true;

    } catch (Exception e) {
        isConnected = false;

    } finally {
        if (connect != null)
            connect.Close();
    }

    return isConnected;
}

While this code works fine, there is a disadvantage. If the server is not online it spends about 4 full seconds trying to open the connection before deciding that it is not available.

Is there a way to test the connection without trying to actually opening it and waiting for the timeout? Something like a database-equivalent of ping?

Answer 1

Yes and no.

You could always note what port your server listens on, and then try and connect to that directly with a socket. Then if you succeed, you can "assume" it's online, but it may not be depending on your network environment. That's the most "ping" like approach though. Of course, it may be something else is listening there, but that's probably unlikely in a standard environment where the DB is just down temporarily.

Also, if your DB is on a dedicated machine, you could actually ping it. But that will only check if the machine is up, not the DB server.

The final option, would be to have some background server constantly monitoring the DB server, and when it detects it is offline, it marks something appropriately. Your app would then check that thing (effectively a cache) and it would be virtually immediate. Of course, it would mean that it may give false-negatives (i.e. the server could be online but the monitoring service hasn't updated the cache yet).

Answer 2

You could just change the connection timeout to be shorter. Do some testing and see how low you can go and still be reliable. I bet you could get close to 500ms.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx