How can I check if SQL server is alive?

Question

I have a method to check and make sure my SQL server is online, which I use in some connection sensitive parts of my code.

Although it works fine, I notice it takes upto 20ms to run, and I was wondering if anyone knows of a better way of checking SQL server to ensure its up and kicking.

Here is my existing code.

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public static bool IsSqlServerOnline(string connectionString)
        {
#if DEBUG || DEBUG_SINGLE_CORE
            Stopwatch sw = new Stopwatch();
            sw.Start();
#endif

#if DEBUG || DEBUG_SINGLE_CORE
            // This sould only occur of we are in the VSTS designer
            if (string.IsNullOrEmpty(connectionString))
            {
                return false;
            }
#endif

            if ( !NetworkInterface.GetIsNetworkAvailable() )
            {
                return false;
            }

            if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException("connectionString");

            bool isSqlServerOnline = false;
            SqlConnectionStringBuilder conString = new SqlConnectionStringBuilder(connectionString);

            try
            {
                using (Ping p = new Ping())
                {
                    string sqlSvr = conString.DataSource;
                    if (sqlSvr != null)
                        isSqlServerOnline = p.Send(sqlSvr).Status == IPStatus.Success;
                }
            }
            catch (PingException)
            {
                isSqlServerOnline = false;
            }

            if ( isSqlServerOnline )
            {
                try 
                {
                    conString.ConnectTimeout = 3;
                    using (SqlConnection conn = new SqlConnection(conString.ToString()))
                    {
                        conn.Open();
                        isSqlServerOnline = true;
                    }
                }
                catch ( Exception )
                {
                    isSqlServerOnline = false;
                }
            }

#if DEBUG || DEBUG_SINGLE_CORE
            sw.Stop();
            Trace.WriteLine(string.Format("IsSqlServerOnline: {0}", sw.ElapsedMilliseconds));
#endif
            return isSqlServerOnline;
        }

Answer 1

You could use the Windows service control manager API:

1. Call OpenSCManager (has hostname as parameter)
2. Call OpenService
3. Call QueryServiceStatus

If the status is SERVICE_RUNNING, the service is up.

If you're not into P/Invoke, have a look at System.ServiceProcess.ServiceController. The function

public ServiceController(string name, string machineName)

looks like it might be used to retrieve the status of a specific service.

Answer 2

As others have mentioned, the 20ms barrier is probably not anything to worry about. However, I might ask how often the code gets called? Would it be sufficient to cache the result of the call for 3-5 seconds (or even just 2-3)? instead of accessing the network each time? 2-3 seconds is a pretty small cache window, and there's nothing to say that a "server alive" check couldn't return OK only to have the server crash in the middle of the code executing anyway. If all you're wasting is 20 ms every few seconds (at most), that's hardly anything to worry about.

Answer 3

Your code checks if you are able to establish a connection to the SQL Server. You may also be interested in taking it one step further and verifying that it is responding to queries by running something like

Select @@version