Test SQL Db Connection

Question

Is there any way to test whether particular database server is connected or not? Like ping operation.

I know some alternate way -test SQL Connection opening. Reason to not use that approach is my app is continuosly at each t seconds test the connection.

Thanks

Answer 1

Not sure why I'm doing this, considering you rarely accept answers, but here goes. Your question brings up a lot of issues, many of the implied. So bear with me....

Connectivity & Me

Connectivity can mean a lot of things. At a much lower layer, as @Paddy points out, PING will tell you if the OS is connected to a network you can reach. But a computer can be connected and SQL could be stopped, offline, or otherwise malfunctioning. SQL could be online, but a firewall blocks access from the requesting computer. The requesting computer could lack permissions. The list goes on.

My point here is that:

The ability to open a connection to SQL Server is the method to test connectivity.

If it were me I would put put a small ADO test on an async-thread. The test would try to open a connection using a short timeout.

Solving symptom or the problem?

SQL Server really is designed, and generally conceived of, as a high availability product. Meaning, if your SQL access is going out so much that you want to test for it (beyond a general error handler) I would suggest you solve the avilability issue. Even the most basic install combined with lots of bad luck should allow for 95% or higher up time.

Using SQL SMO to test

If I haven't dissuaded you by this point, there is an alternative. SQL SMO (the replacement for SQL DMO) is a series of objects that allow programmatic access to SQL server management.

The code below will require references to a few objects:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.SqlEnum.dll
• Microsoft.SqlServer.SQLWmiManagmenet.dll
• Microsoft.SqlServer.WmiEnum.dll

You can use this getting started article to see how to setup a project. Note you will need WINDOWS permissions (as opposed to SQL authentication) to connect to the SQL Server in question.

Now onto the code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Wmi;

namespace SQL_SMO_Test {
 class Program {

    public const string SQL_SERVICE_BASENAME = "MSSQL";

    public static bool SQLServiceIsRunning (string SQLServerName ) {
      string[]           nameComponents;
      string             ComputerName;     
      string             InstanceName;     
      string             ServiceName;          
      ManagedComputer    mc;
      ServiceCollection  serviceList;

      //split up SQL Name
      nameComponents = SQLServerName.Split('\\');
      ComputerName   = nameComponents[0];
      InstanceName   = nameComponents.Length > 1 ? nameComponents[1] : "";
      ServiceName    = SQL_SERVICE_BASENAME + 
                           ((InstanceName.Length>0) ? "$" + InstanceName : "");

       //get WMI Computer object
       mc          = new ManagedComputer(ComputerName);
       serviceList = mc.Services;


       //find any SQL Services & See if it's our instance
       foreach (Service s in serviceList) {
          if (s.Type.Equals(ManagedServiceType.SqlServer) ) {
             Console.WriteLine("Service Found --" + s.Name);
             if ( (!ServiceName.Contains('$')) || s.Name == ServiceName ) {
                Console.WriteLine("..Correct Instance found" + s.Name);
                if (s.ServiceState.Equals(ServiceState.Running) ){
                    Console.WriteLine("....Service is Running");
                    return true;
                }
             }
          }
       } //end foreach()

       Console.WriteLine("service not found, or stopped");
       return false; //service not found, or stopped

    } //end SQLServiceIsRunning

                        /* format: servername\instanceIfAny */ 
    public static bool SQLIsRunning( string SQLServerName ) {
      Server  srv;

      Console.WriteLine("SERVICE RUNNING (" + SQLServerName + ") ?" );
      Console.WriteLine("------------------------------" );

      if (! SQLServiceIsRunning(SQLServerName) ) {
         return false;
      }
      Console.WriteLine("------------------------------" );

      srv = new Server(SQLServerName);
      try {
          Console.WriteLine("Status: " + srv.Status.ToString() );
          return true;
      } catch (Exception ex) {
          Console.WriteLine("Exception: " + ex.ToString() );
      }

       return false;

    } //end SQLIsRunning()


    static void Main(string[] args) {
      bool result = SQLIsRunning("MST-SQL01.mst.com");

      Console.WriteLine("-----------");
      Console.WriteLine("Press any key to close...");
      Console.ReadKey();


    } //end main()
  }
}

Answer 2

You can ping a server:

http://msdn.microsoft.com/en-us/library/system.net.networkinformation.ping.aspx

But this will not tell you if the DB is up. Opening a connection (which authenticates etc. is probably your only way). Why are you continually pollling this way?