What's the fastest method to check SQL server availability?

Question

What's the best method to check if SQL server exists or not?

I'm trying Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion() and it works fine if server exists and available. But it kinda pretty slow, if there is no such a server.

Is there any fast enough method to check without even defining user credentials (only the server name), if a server exists?

What do you recommend to use?

Gorrono

How is this useful? Under what scenario would someone deploy a database application not knowing if there is a database? – Incalculable

We really need to know to what extent a slow responding server is treated as non-available. – Zambia

Answer 1

You could just use TcpClient class to query the server and check if a specific port is open, could be something like this:

using System.Net;
using System.Net.Sockets;

public bool CheckServerAvailablity(string serverIPAddress, int port)
{
  try
  {
    IPHostEntry ipHostEntry = Dns.Resolve(serverIPAddress);
    IPAddress ipAddress = ipHostEntry.AddressList[0];

    TcpClient TcpClient = new TcpClient();
    TcpClient.Connect(ipAddress , port);
    TcpClient.Close();

    return true;
  }
  catch
  {
    return false;
  }
} 

Ameeameer

For me, this worked fine with default instances but failed for non-default instances. I found this solution and it works in all cases: codeproject.com/Articles/612751/… – Calicut

Answer 2

You could still use Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion() but use it asynchronously. e.g. you could call it via a BackWorker class. The DoWork event would call Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion(). The RunWorkerCompleted would just set a boolean variable to true. THat way you could fire it off, wait however long you wanted, check the boolean value and if it was not true then you would know that the SQL server had not responded yet and you could cancel the BackgroundWorker.

Naidanaiditch

Answer 3

You could try and open a tcp socket to port 1433 (default sql port) with a short timeout and see if it responds.

This requires the SQL server to have the TCP/IP protocol enabled.

Castello

Answer 4

To add to Mikael's, you could also ping the host first, as that will respond the quickest if the server is down.

Of course, this all assumes that you are trying to get to a remote server over TCP/IP.

Platyhelminth

Answer 5

After using Ben Robinson's answer I came up with this and it works good for me. I was using the connection string to open and then close a connection in a try block but when I ran on Windows 8.1 the exception was never caught and the program crashed.

public unsafe bool OdbcConnectionTest(string sConnectionString
    , out int actualTimeMs)
{
    DateTime dtme = DateTime.Now;
    OdbcConnectionStringBuilder con;
    Microsoft.SqlServer.Management.Smo.Server svr;
    Microsoft.SqlServer.Management.Common.ServerVersion sVer;
    Microsoft.SqlServer.Management.Smo.Database db;
    try
    {
        con = new System.Data.Odbc.OdbcConnectionStringBuilder(sConnectionString);
        object sServer;
        if (con.TryGetValue("server", out sServer))
        {
            svr = new Microsoft.SqlServer.Management.Smo.Server((string)sServer);
            if (svr != null)
            {
                sVer = svr.PingSqlServerVersion((string)sServer);
                if (sVer != null)
                {
                    object sDb;
                    if (con.TryGetValue("database", out sDb))
                    {
                        if (!String.IsNullOrWhiteSpace((string)sDb))
                        {
                            db = svr.Databases[(string)sDb];
                            if (db != null && db.IsAccessible)
                            {
                                TimeSpan ts = DateTime.Now - dtme;
                                actualTimeMs = (int)ts.TotalMilliseconds;
                                return true;
                            }
                        }
                    }
                }
            }
        }
    }
    catch 
    {
        actualTimeMs = -1;
        return false;
    }
    actualTimeMs = -1;
    return false;
}

Abortive