Check if server exists

Question

Here is the problem. User can enter server name and connection string for database. If server is not accessible (wrong address, firewalls or any other issue), I want to be aware of this as quickly as possible.

If I'm using sqlConnection and try to connect to a non-accessible server this takes very long (I think more than 1 minute!). This has nothing to do with connection timeout btw so setting this property won't help.

My idea is to first try to ping server and if I get response (which means server is accessible from the point of application), than proceed with sqlConnection. If there is no response from ping, operation is aborted and user is properly notified.

Is there any better way of doing this? Any idea would be welcome.

Forgot to point out, I'm using NHibernate so I can't use any MSSQL specific library. Also, target server can have Linux as OS, not just Windows.

Answer 1

This previous question's answer has a decent approach that is more elegant that just a ping, using WMI will give you more info overall

Answer 2

I think its worth noting that the only really good way to check a generic database's existence and accessibility is to access it. A machine could be configured with a perfectly operational database, and be refusing pings, for example. Similarly, a machine could be running a DB instance and be configured to ignore/refuse all WMI queries. If you can assume for your environments that these are not true (i.e., you know that all your companies machines will always answer pings) then you can continue; otherwise, you might have to just take the 'attempting to connect' hit.

Answer 3

could you use the SqlDataSourceEnumerator to get a DataTable with all available SQLServers, and then check the users input against the list. Something like this. See MSDN.

System.Data.DataTable table = VisibleServerList.GetVisibleServers();

public static class VisibleServerList
{
    public static System.Data.DataTable GetVisibleServers()
    {
        System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance;
        return instance.GetDataSources();
    }
}

Answer 4

If you're using SQL Server you could use the SQLDMO library, which contains a method that will enumerate available servers on the network.

Answer 5

When creating the connection to the server, set the connection timeout to something like 2 or 3 seconds. This way it will bail out much faster if the server isn't actually there.

The only downside is if the server does exist, and it's being hammered, then you might get a false negative. But that's rarely a problem.

Answer 6

At the end, I have decided to take hybrid approach. When user hits "Connect" button, ping will be performed on the server user has entered. If there is no response from the server (which can be detected very fast), user will get the message that server seems to be unaccessible, and a question if he wants to continue and try to connect to that server.

Basically, user will decide wheather he will wait for "attempting to connect".