Detecting if SQL server is running

Question

I'm looking for a way to poll different servers and check that SQL server is up and running. I'm writing my code in C#. I don't particularly care about individual databases, just that SQL server is running and responsive.

Any ideas?

Answer 1

Well, the brute force solution is to attempt to initiate a connection with the database on each server. That will tell you whether it's running, though you could have timeout issues.

The more elegant (but more difficult... isn't that always the way?) solution would be to use WMI to connect to the remote machine and find out if the SQL server process is running.

Answer 2

Use the TCPClient Class to create a generic function that connects in TCP to a given IP address.

Then iterate over the list of servers you want to test and try to open a connection to port 1433.

Answer 3

If you need specific servers, use WMI. If you just want all available servers:

http://support.microsoft.com/kb/q287737/

Answer 4

I would certainly go with Vincent's answer. Just make absolutely certain you are closing and disposing the tcp connections properly etc. WMI seems a bit of overkill to me if that is all you're after.

Answer 5

@Vincent: That also requires that your servers are running on port 1433. That's not necessarily a foregone conclusion if they're not under your control.

Answer 6

Great ideas all! I'll try Vincent's approach and know that I have a couple of fall back ideas!

Answer 7

Follow up to question - I tried using WMI, and only get partial results (I know there are more SQL server instances out there).

string serversStr = ValueGetter.getXmlConfigurationStringValue(ConfigurationValues.SQL_SERVERS_MONITORED);
  string sqlServerServiceName = ValueGetter.getXmlConfigurationStringValue(ConfigurationValues.SQL_SERVER_SERVICE);

  string[] servers = serversStr.Split(',');
  string path;

  StringBuilder sb = new StringBuilder(DateTime.Now + "\nThe following system(s) status was recorded:\n\n" );
             bool reportNeeded = false;

            sb = new StringBuilder(DateTime.Now + "\nThe following system(s) status was recorded:\n\n");

            buildServerMap();


            if (isThreadEnabled())
            {
                foreach (string sqlServer in servers)
                {
                    if (serverFound(sqlServer))
                    {
                        sb2.Append(sqlServer + " SQL Server - FOUND\n");
                    }
                    else
                    {
                        sb2.Append(sqlServer + " SQL Server - UNVERIFIABLE\n");
                    }

                    try
                    {
                        path = @"\\" + sqlServer + @"\root\cimv2";

                        ManagementScope ms = new ManagementScope(path);

                        ms.Connect();

                        ManagementObjectSearcher searcher = new ManagementObjectSearcher("SELECT * FROM Win32_Service WHERE Started = TRUE AND DisplayName='" + sqlServerServiceName + "'");
                        searcher.Scope = ms;

                        if (searcher != null && searcher.Get() != null)
                        {
                            foreach (ManagementObject service in searcher.Get())
                            {
                                sb.Append(sqlServer + " SQL Server service - RUNNING\n");
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        sb.Append(sqlServer + " SQL Server service - UNVERIFIABLE\n");
                        reportNeeded = true;
                    }

And i also tried getting all the instances using this code:

private void buildServerMap()
    {
        sqlServersMap = new Dictionary<string, string>();

        //get all available SQL Servers     
        SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
        SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();

        ArrayList servs = new ArrayList();
        for (int i = 0; i < sqlServers.Count; i++)
        {
            object srv = sqlServers.Item(i + 1);

            if (srv != null)
            {
                sqlServersMap.Add(srv.ToString(), srv.ToString());
            }
        }
    }

    private bool serverFound(string serverName)
    {
        bool found = false;

        string value = null;
        sqlServersMap.TryGetValue(serverName, out value);

        if (value != null)
        {
            found = true;
        }

        return found;
    }

So I am getting accurate results for 1 out 6 sql instances with the first code snippet (WMI), and 3 out of 6 using the COM DMO.

Any ideas??

Answer 8

System.Data.Sql.SqlDataSourceEnumerator will return all instances of SQL Server currently running.
MSDN Link

Answer 9

This could help if you have an administrator account.