Enumerating local 64 bit and 32 bit instances of SQL Server

When checking for local instances of SQL Server its normally best enumerate the value of the registry key HKLMSoftwareMicrosoftMicrosoft SQL Server and then look at the value on installed instances.

However on a 64 bit machine there are two keys, the 64 bit version and the 32 bit version for the machine so using the standard method will not work:

            public static string[] EnumLocalInstances()

            {

                  RegistryKey rk =

                  Registry.LocalMachine.OpenSubKey(

                  @”SOFTWAREMicrosoftMicrosoft SQL Server”);

                  return rk.GetValue(“InstalledInstances”) as String[];

            }

This will return just the 64 bit or the 32 bit instances depending if you are running in WOW or not.

To get both the 64 bit and 32 bit instances you need to use P/Invoke – the following code should do the job:

      internal class ServerDiscovery

      {

            [DllImport(“advapi32.dll”, CharSet = CharSet.Unicode, EntryPoint = “RegOpenKeyEx”)]

            static extern int RegOpenKeyEx(

                        UIntPtr hKey,

                        string subKey,

                        uint options,

                        int sam,

                        out IntPtr phkResult);

            [DllImport(“advapi32.dll”, SetLastError = true)]

            static extern int RegQueryValueEx(

                        IntPtr hKey,

                        string lpValueName,

                        int lpReserved,

                        out uint lpType,

                        IntPtr lpData,

                        ref uint lpcbData);

            const int KEY_QUERY_VALUE = 0x1;

            const int KEY_SET_VALUE = 0x2;

            const int KEY_CREATE_SUB_KEY = 0x4;

            const int KEY_ENUMERATE_SUB_KEYS = 0x8;

            const int KEY_NOTIFY = 0x10;

            const int KEY_CREATE_LINK = 0x20;

            const int KEY_WOW64_32KEY = 0x200;

            const int KEY_WOW64_64KEY = 0x100;

            const int KEY_WOW64_RES = 0x300;

            static UIntPtr HKEY_LOCAL_MACHINE = (UIntPtr)0x80000002; 

            public static bool Is64BitMachine

            {

                  get

                  {

                        try

                        {

                              ManagementObjectSearcher searcher = new ManagementObjectSearcher(“SELECT AddressWidth FROM Win32_Processor”);

                              ManagementObjectCollection collection = searcher.Get();

                              foreach (ManagementObject obj in collection)

                              {

                                    return (UInt16)(obj[“AddressWidth”]) == (UInt16)64;

                              }

                        }

                        catch(Exception)

                        {

                              ;

                        }

                       

                        return false;

                  }

            }

            internal static string[] GetLocalServers()

            {

                  if(Is64BitMachine)

                  {

                        String[] instances64Bit = DoGetLocalInstances(KEY_WOW64_64KEY);

                        String[] instances32Bit = DoGetLocalInstances(KEY_WOW64_32KEY);

                        string[] returnValue = new string[instances32Bit.Length + instances64Bit.Length];

                        instances32Bit.CopyTo(returnValue, 0);

                        instances64Bit.CopyTo(returnValue, instances32Bit.Length);

                        return returnValue;

                  }

                  else

                  {

                        return DoGetLocalInstances(0);

                  }

            }

            private static string[] DoGetLocalInstances(int key)

            {

                  string[] returnValue = new string[0];

                 

                  try

                  {

                        IntPtr hResult;

                        int res = RegOpenKeyEx(HKEY_LOCAL_MACHINE,

                                                         @”SOFTWAREMicrosoftMicrosoft SQL Server”,

                                                         0,

                                                         KEY_QUERY_VALUE | key,

                                                         out hResult);

                        if (0 == res)

                        {

                              uint type;

                              uint dataLen = 0;

                              RegQueryValueEx(hResult, “InstalledInstances”, 0, out type, IntPtr.Zero, ref dataLen);

                              byte[] databuff = new byte[dataLen];

                              byte[] temp = new byte[dataLen];

                              List<String> values = new List<string>();

                             

                              GCHandle handle = GCHandle.Alloc(databuff, GCHandleType.Pinned);

                              try

                              {

                                    RegQueryValueEx(hResult, “InstalledInstances”, 0, out type, handle.AddrOfPinnedObject(), ref dataLen);

                              }

                              finally

                              {

                                    handle.Free();

                              }

                             

                              int i = 0;

                              int j = 0;

                              while (i < databuff.Length)

                              {

                                    if (databuff[i] == ”)

                                    {

                                          j = 0;

                                          string str = Encoding.Default.GetString(temp).Trim(”);

                                          if (String.IsNullOrEmpty(str) == false)

                                          {

                                                values.Add(str);

                                          }

                                          temp = new byte[dataLen];

                                    }

                                    else

                                    {

                                          temp[j++] = databuff[i];

                                    }

                                    i++;

                              }

                              returnValue = new string[values.Count];

                              values.CopyTo(returnValue);

                        }

                       

                  }

                  catch(Exception)

                  {

                        ;

                  }

                  for (int i = 0; i < returnValue.Length; i++)

                  {

                        if (returnValue[i] == “MSSQLSERVER”)

                        {

                              returnValue[i] = Environment.MachineName;

                        }

                        else

                        {

                              returnValue[i] = Environment.MachineName + @”” + returnValue[i];

                        }

                  }

                  return returnValue;

            }

}