views:

252

answers:

6

In my Windows Forms application (on startup) I use the ping command to check if both the Internet connection and my SQL Server are alive.

Is there any "better" way-command I should use for accomplishing the above task in the .NET framework 2.0?

+2  A: 

Ping can only be used to verify that packets can travel to the destination but is not suitable to check if the actual server is operational.

If your server is configured to provide SNMP, it would be a better choice to acquire SNMP information to know if your server is operational.

However, programming SNMP is not a simple task. Go for it if you really need the benefits.

Andrew Keith
+7  A: 

Just make a small, simple request to your SQL server to see if the connection works. Other than that, why not just a sensibly-handled exception to kick in if your connection times out or otherwise fails?

Peter
Microsoft does this themselves - a SQL query to ask if the server is still alive. Basically "SELECT 'Are you alive?' and parsing the result.
Randolph Potter
If ICMP traffic is blocked, Ping will fail while the database connection works fine. The small simple request idiom is usuallly SELECT 1; for MS-SQL or SELECT 1 FROM DUAL; for other database systems.
bbuser
A: 

To test the internet connection. The System.Net has allot of useful functions. HttpWebRequest function will attempt to load a website and you will get a detailed response to use in testing the connection.

Joel
A: 

Here is a sample code:

private bool IsInternetAvailable()
        {
            bool ret = false;

            try
            {
                HttpWebRequest req = (HttpWebRequest)
      HttpWebRequest.Create("http://www.yourremoteserver.com/");
                HttpWebResponse res 
      = (HttpWebResponse)req.GetResponse();
                if (res.StatusCode == HttpStatusCode.OK)
                {
                    ret = true;
                }
                else
                {
                    ret = false;
                }
                res.Close();
            }
            catch
            {
                ret = false;
            }

            return ret;
        }

Source: my own blog

For SQL Server, it is better to 'try a connection'.

NinethSense
What if Google is down? It happens
Charlie Somerville
You need to use 'YOUR REMOTE' server always. Google link is just for example.
NinethSense
+1  A: 

To check network and MS-SQL Server without executing a command:

// Setup a timer to call as needed
public bool IsSqlConnectionOpen(string connectionString)
{
    bool open = false;
    using (var connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            open = true;
            connection.Close();
        }
        catch (SqlException e)
        {
            // log e.ToString()
        }
    }
    return open;
}

Modified from code I use to get SQL Server version.

Update: mattcodes raises a good point about connection pooling, according to MSDN

The pool is automatically cleared when a fatal error occurs, such as a failover.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

If that's not enough, set Pooling=false in the connection string to be sure.

Si
Is there a risk that this returns one from the connection pool, that has subsequently became victim to a transport or other problem between client and server during the timeout period?
mattcodes
Good point Matt, I'll update with my findings.
Si
A: 

Be very careful to keep this as start-up logic. I have see things like this degenerate to heart beat logic which has a lot of other fun issues. One of which is, if you app will have many running instances. If your app is used in a large environment and there might be thousands of instances running, then heartbeat logic can add to network and server loads. Or sometimes the heartbeat interval is configurable and someone sets it to zero or some other crazy value.

One other point, I have seen this combined where the question ask of the DB server is what time is it on the server. i.e. select GetDate()

This does two things, first you know the server and network are working if you get an answer. Second if you have some time critical operations you can measure the clock skew between the client machine and the DB server. Sometimes this is important to know.

John Dyer