Detect local SQL server installation with C#(32 bit as well as 64 bit)

Question

How can I detect if SQL is installed on the local machine using C#? Is it possible to check a remote machine?

Answer 1

You've got several ways to do it:

• SmoApplication.EnumAvailableSqlServers()
• SqlDataSourceEnumerator.Instance
• Direct access to system registry

Direct access isn't the recommended solution by MS, because they can change keys/paths. But the other solutions fails providing instances on 64-bit platforms.

Therefore I prefer to check SQL Server instances in System Registry. Doing that, keep in mind the difference in Registry access between x86 and x64 platforms. Windows 64-bit stores data in different parts of system registry and combines them into views. So using RegistryView (available since .NET 4) is essential.

using Microsoft.Win32;

RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;
using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
{
    RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
    if (instanceKey != null)
    {
        foreach (var instanceName in instanceKey.GetValueNames())
        {
            Console.WriteLine(Environment.MachineName + @"\" + instanceName);
        }
    }
}

If you are looking for 32-bit instances on a 64-bit OS (pretty weird, but possible), you will need to look:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server

Answer 2

You can check registry path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances

For an example of code that does similar work, see this quesion how-to-get-sql-server-installation-path-programatically

Please also see MSDN: File Locations for Default and Named Instances of SQL Server for more details on registry keys used by SQL Server.

Answer 3

We check the registry for that

Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion", ", "0.0.0.0");

Answer 4

You could use the System.Management namespace to check for the existence of SQL Server in the server's running services.

Answer 5

You will need SQL 2005 Backwards Compatibility redist. See: How to Connect to Sqlserver2008 using SMO any workaround has to be done?

using Microsoft.SqlServer.Management.Smo;

DataTable dt = SmoApplication.EnumAvailableSqlServers(true);
         string[] szSQLInstanceNames = new string[dt.Rows.Count];
         StringBuilder szSQLData = new StringBuilder();

         if (dt.Rows.Count > 0)
         {
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
               try
               {
                  szSQLInstanceNames[i] = dr["Name"].ToString();
                  Server oServer;
                  oServer = new Server(szSQLInstanceNames[i]);
                  if (string.IsNullOrEmpty(dr["Instance"].ToString()))
                  {
                     szSQLInstanceNames[i] = szSQLInstanceNames[i] + "\\MSSQLSERVER";
                  }
                  szSQLData.AppendLine(szSQLInstanceNames[i] + "  Version: " + oServer.Information.Version.Major + "  Service Pack: " + oServer.Information.ProductLevel + "  Edition: " + oServer.Information.Edition + "  Collation: " + oServer.Information.Collation);
               }
               catch (Exception Ex)
               {
                  szSQLData.AppendLine("Exception occured while connecting to " + szSQLInstanceNames[i] + " " + Ex.Message);
               }

               i++;
            }

Note: if you just want to see if Default intance is installed or no just do:

Server oServer; oServer = new Server(Environment.MAchineName);

if it does not throw an exception, the SQL exists.

Answer 6

Perhaps you'll find the following useful. Use first method to find about servers (local & network), then you can use the second to enumerate databases on each server.

using System;
using System.Collections.Generic;
using System.Data.Sql;
using System.Data;
using System.Data.SqlClient;

namespace Info.Data.Engine.SQLServer
{
  public static class SQLServerHelper
  {
    public static List<String> EnumerateServers()
    {
      var instances = SqlDataSourceEnumerator.Instance.GetDataSources();
      if ((instances == null) || (instances.Rows.Count < 1)) return null;

      var result = new List<String>();
      foreach (DataRow instance in instances.Rows)
      {
        var serverName = instance["ServerName"].ToString();
        var instanceName = instance["InstanceName"].ToString();
        result.Add(String.IsNullOrEmpty(instanceName) ? serverName : String.Format(@"{0}\{1}", serverName, instanceName));
      }
      return result;
    }

    public static List<String> EnumerateDatabases(String connectionString)
    {
      try
      {
        using (var connection = new SqlConnection(connectionString))
        {
          connection.Open();
          var databases = connection.GetSchema("Databases");
          connection.Close();
          if ((databases == null) || (databases.Rows.Count < 1)) return null;

          var result = new List<String>();
          foreach (DataRow database in databases.Rows)
          {
            result.Add(database["database_name"].ToString());
          }
          return result;
        }
      }
      catch
      {
        return null;
      }
    }
  }
}

HTH, Dejan

Answer 7

You could just open a connection to the machine and close it. If you throw an exception that's a decent sign. I realize it's not super clean but it'll get the job done.

Answer 8

Thanks a lot to Dejan Stanič.
And I wanted to add more search criteria:

Check if SQL Server 2008 is installed on local machine in .net sqlclient