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?
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?
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.
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?
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.
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'.
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.
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.