CODEWITHSUNDEEP
c#sql-serverwinformsnetwork-programming
Refracted Paladin
Refracted Paladin

Reputation: 12216

Fastest way to tell if SQL Server is available

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.

Upvotes: 2

Views: 2485

Answers (5)

Alex_L
Alex_L

Reputation: 2654

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

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

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.

Upvotes: 2

Troggy
Troggy

Reputation: 644

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.

Upvotes: 0

ramnik
ramnik

Reputation: 545

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

Upvotes: 0

JP Alioto
JP Alioto

Reputation: 45117

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.

Upvotes: 3

Related Questions