How to detect SQL Server installation?

Question

I'm developing an application which needs SQL Server 2014 (or newer) LocalDB (or express, standard, whatever!) as a prerequisite to work. And I'm using Inno Setup to build an installer for my app.

I don't want to install SqlLocalDB with Inno Setup, I just want to inform user that he needs to install that in order to application works.

There are some topics here and elsewhere most of them explaining that I should look into registry to find it out. But each one suggests different keys and I'm really confused. In addition, my own registry completely differs!

I need something like this;

If (SQLServer2014ExpressOrNewerInstalled) then
    result := true;
else if (SQLServer2014LocalDBOrNewerInstalled) then
    result := true;
else
    result := false;

Thanks in advance.

Answer 1

Using WMI to seek the information about the SQL server version installed. There are various possible issues of failures in this method also. But much better than the Registry solution.

I have searched for an alternative solution as a fall back if the registry keys are compromised for whatsoever reasons.

GetWmiNameSpace

Gets the list of namespaces available under SQL server node. Qualify the namespaces you would like to be returned to the GetSqlServerValidationResults for fetching further info

GetSqlServerValidationResults

Actual version checking function. I was very much interested to get a name value pair to avoid bloating the if conditions in the function. Due to the time constraint couldn’t allocate more time to change to name value list. You may need to change the numbers to check the version you would like to compare

function GetWmiNameSpace(): String;
var
  oWMI, oServer, oProvider, oInstances, collSubNamespaces: Variant;
  i: Integer;
  item: Variant;
begin
  oWMI := CreateOleObject('wbemScripting.SWbemLocator'); 
  oServer := oWMI.ConnectServer('.', 'root/Microsoft/sqlserver');
  collSubNamespaces  := oServer.ExecQuery('select * from __namespace');
  for i := 0 to collSubNamespaces.Count - 1 do
  begin
    item := collSubNamespaces.ItemIndex[i];
    if Pos('ComputerManagement', item.Name) = 1 then Result := item.Name;
    Log('Namespaces are ' + item.Name);
  end; 
end;  

function GetSqlServerValidationResults(): String;
var
  Query: string;
  WbemLocator, WbemServices, WbemObjectSet: Variant;
  server: Variant;
  I: Integer;
  versions: TStringList;
begin
  WbemLocator := CreateOleObject('WbemScripting.SWbemLocator');
  WbemServices :=
    WbemLocator.ConnectServer(
      '.', 'root\Microsoft\SqlServer\' + GetWmiNameSpace());
  Query := 'SELECT * FROM SqlServiceAdvancedProperty';
  WbemObjectSet := WbemServices.ExecQuery(Query);
  if not VarIsNull(WbemObjectSet) and (WbemObjectSet.Count > 0) then
  begin
    for I := 0 to WbemObjectSet.Count - 1 do
    begin
      server := WbemObjectSet.ItemIndex(I);
      if not VarIsNull(server) then
      begin
        if server.PropertyName = 'VERSION' then
        begin
            Log('Name' + server.PropertyName);
            if not VarIsNull(server.PropertyStrValue) then 
            begin
              Log('value ==> ' + server.PropertyStrValue + '[' +
                IntToStr(pos('13.0.',server.PropertyStrValue)));
              if pos('10.0.', server.PropertyStrValue) = 1 then Result  := '2008';
              if pos('10.5', server.PropertyStrValue) = 1 then Result  := '2008R2';
              if pos('11.0.', server.PropertyStrValue) = 1 then Result  := '2012';
              if pos('12.0.', server.PropertyStrValue) = 1 then Result  := '2014';
              if pos('13.0.', server.PropertyStrValue) = 1 then Result  := '2016';
            end;
        end;        
      end;
    end;
  end;
end;