Fastest way to tell if SQL Server is available

Question

I have a winform program that uses Merge Replication to keep a local SQL Express sync'd with a Central SQL Server on our network. Due to the nature of this app the sync process runs automatically on App open and close. The way I am doing it (below) seems quite time consuming. What would be a faster way?

Thinking outside the box it would probably be okay if I just checked for the Server itself though that could present a hole for an edge case where the Server is available but SQL is down. Alternately, maybe just checking if our domain is available though that could leave the same hole I guess.

Thanks!

private static bool IsSqlAvailable()
    {
        try
        {
            var conn = new SqlConnection("Data Source=WWCSTAGE;Initial Catalog=Connect;Integrated Security=True");
            conn.Open();
            conn.Close();
            HUD.IsSQLAvailable = true;
            return true;
        }
        catch (Exception)
        {
            HUD.IsSQLAvailable = false;
            return false;
        }
    }

---

As an additional note. The above is particularly slow when off network. I even set the Connection Timeout = 1 and it still sits at this point for quite some time.

Answer 1

I would wrap my Connection in a using clause and probably lower the connection timeout in my connection string (generally, if the server is up, it should connect more quickly than the default), but other than that, it's fine.

Answer 2

You may be thinking about this too hard. The availability of your SQL server is essentially equivalent to you being able to connect to it, so I would say your method of trying to open a connection is quite valid. My only suggestion would be to assign HUD.IsSQLAvailable once where you call the method since you are already getting a return value, instead of assigning it twice inside IsSqlAvailable().

Answer 3

I just wrote a test connection method today in an app I am writing at work.

I just use a simple try catch method on just a connection.open and connection.close and catch the exception if it fails. You dont have to set a variable if you dont need it. Just return true or false. It will hang for a few seconds if the connection is going to fail.

Answer 4

My usual advice in this kind of scenario, where you're checking for the existence/connectivity of something that is out of your control is: don't.

Your check can pass and an instant later, the result is no longer true. So just try to do whatever it is you want to do, assuming that the SQL server is available, and handle the inevitable errors gracefully.

Answer 5

I think you can just ask the SQL Server TCP port. But there is a problem if used named instances.