Bootstrapping SQL Express from WiX?

Question

I'm working on a WPF app, and using WiX as an installer.

I'd like to start using SQL Express 2012, but want to resolve installer issues first.

I'm looking for a full-up example of detecting, bootstrapping, installing, upgrading and uninstalling SQL Express 2012 using WiX (although partials will be useful, too).

Also, most of the detection logic I've found so far on the web uses registry keys. However, Microsoft recommends using WMI instead (see http://blogs.msdn.com/b/sqlexpress/archive/2006/07/29/faq-detecting-sql-server-2005-using-wmi.aspx). Is that possible using WiX?

Answer 1

This is what I have, hope it helps:

<?define ServerInstall="SomeCondition" ?>

<?define InstanceName = "YOUR_INSTANCE" ?>
<?define SqlWebLink = http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLEXPR_x86_ENU.exe ?>

<Variable Name="SqlVariable" Type="string" Value="/SAPWD=some_password" Hidden="yes" />

<!-- Read SQL Server keys to find current instance and version -->
<util:RegistrySearch
  Id="SqlInstanceKeyFound"
  Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" Value="$(var.InstanceName)"
  Result="exists" Variable="SqlInstanceKeyFound" />
<util:RegistrySearch
  Id="SqlInstanceKey"
  Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" Value="$(var.InstanceName)"
  Variable="SqlInstanceKey" After="SqlInstanceKeyFound" Condition="SqlInstanceKeyFound" />
<util:RegistrySearch
  Id="SqlInstanceFound"
  Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\[SqlInstanceKey]"
  Result="exists" Variable="SqlInstanceFound" After="SqlInstanceKey" Condition="SqlInstanceKeyFound" />
<util:RegistrySearch
  Id="SqlVersion"
  Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\[SqlInstanceKey]\Setup" Value="Version"
  Variable="SqlVersion" After="SqlInstanceKey" Condition="SqlInstanceFound" />

<PackageGroup Id="Sql2012Express">
  <!--
    SQL Server 2012 Express - Install new instance
    http://msdn.microsoft.com/en-us/library/ms144259.aspx
    SQL Server Express requires WIndows Installer 4.5
    RepairCommand="/ACTION=Repair /INSTANCENAME=$(var.InstanceName) /Q /HIDECONSOLE"
  -->
  <ExePackage Id="Sql2012Express"
    DisplayName="SQL Server 2012 Express"
    Cache="yes"
    Compressed="no"
    PerMachine="yes"
    Permanent="no"
    Vital="yes"
    Name="Redist\SQLEXPR_x86_ENU.exe"
    SourceFile="..\Packages\SQLEXPR_x86_ENU.exe"
    DownloadUrl="$(var.SqlWebLink)"
    InstallCommand="/ACTION=Install /INSTANCENAME=$(var.InstanceName) /FEATURES=SQL /SECURITYMODE=SQL [SqlVariable] /TCPENABLED=1 /SQLSVCACCOUNT=&quot;NT AUTHORITY\NETWORK SERVICE&quot; /SQLSVCSTARTUPTYPE=Manual /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /ADDCURRENTUSERASSQLADMIN=FALSE /Q /HIDECONSOLE /SkipRules=RebootRequiredCheck /IAcceptSQLServerLicenseTerms"
    UninstallCommand="/Action=Uninstall /INSTANCENAME=$(var.InstanceName) /FEATURES=SQL /Q /HIDECONSOLE"
    DetectCondition="SqlInstanceFound"
    InstallCondition="$(var.ServerInstall)">
    <ExitCode Value ="3010" Behavior="forceReboot" />
    <dep:Provides DisplayName="Net2 SQL Server 2012 Express" Key="SQLServer2012Express,$(var.InstanceName)" Version="11.0.3000.0" />
  </ExePackage>

  <!--
    SQL Server 2012 Express - Upgrade existing pre-SQL 2012 instance
  -->
  <ExePackage Id="Sql2012ExpressUpgrade"
    DisplayName="SQL Server 2012 Express Upgrade"
    Cache="no"
    Compressed="no"
    PerMachine="yes"
    Permanent="yes"
    Vital="yes"
    Name="Redist\SQLEXPR_x86_ENU.exe"
    SourceFile="..\Packages\SQLEXPR_x86_ENU.exe"
    DownloadUrl="$(var.SqlWebLink)"
    InstallCommand="/ACTION=Upgrade /INSTANCENAME=$(var.InstanceName) /Q /HIDECONSOLE /SkipRules=RebootRequiredCheck /IAcceptSQLServerLicenseTerms"
    DetectCondition="NOT (SqlInstanceFound AND (SqlVersion &lt; v11.0.0.0))"
    InstallCondition="$(var.ServerInstall)">
    <ExitCode Value ="3010" Behavior="forceReboot" />
  </ExePackage>

  <!--
    SQL Server 2012 SP1 Express - Upgrade existing SQL 2012 instance to SP1
  -->
  <ExePackage Id="Sql2012ExpressEditionUpgrade"
    DisplayName="SQL Server 2012 SP1 Express Patch"
    Cache="no"
    Compressed="no"
    PerMachine="yes"
    Permanent="yes"
    Vital="yes"
    Name="Redist\SQLEXPR_x86_ENU.exe"
    SourceFile="..\Packages\SQLEXPR_x86_ENU.exe"
    DownloadUrl="$(var.SqlWebLink)"
    InstallCommand="/ACTION=Patch /INSTANCENAME=$(var.InstanceName) /Q /HIDECONSOLE /SkipRules=RebootRequiredCheck /IAcceptSQLServerLicenseTerms"
    DetectCondition="NOT (SqlInstanceFound AND (SqlVersion &gt; v11.0.0.0) AND (SqlVersion &lt; v11.0.3000.0))"
    InstallCondition="$(var.ServerInstall)">
    <ExitCode Value ="3010" Behavior="forceReboot" />
  </ExePackage>

You would need to change the install commands to match your requirements.

Answer 2

No suggestions worked for me until following 2 changes:

1. set util:RegistrySearch/@Win64 attribute value to "yes" (default is "no", and it's ok for 32bit systems)
   
   

2. remove ExePackage/@DetectCondition attribute at all (don't aware the cause)

Below is working example:

    <util:RegistrySearch Id="SqlInstanceKeyFoundSearch"
                         Root="HKLM"
                         Key="SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
                         Value="SQLEXPRESSENGINE"
                         Result="exists"
                         Variable="SqlInstanceKeyFound"
                         Win64="yes" />

    <PackageGroup Id="SQLServerExpress">
        <ExePackage Compressed="no"
                    DisplayName="Installing SQL Server Express 2014"
                    PerMachine="yes"
                    Cache="yes"
                    Vital="yes"
                    Permanent="no"
                    InstallCommand='/IACCEPTSQLSERVERLICENSETERMS /HIDECONSOLE /INSTANCEID="$(var.InstanceName)" /ACTION="Install" /FEATURES=SQLENGINE /HELP="False" /INDICATEPROGRESS="False" /QUIET="True" /QUIETSIMPLE="False" /ERRORREPORTING="False" /SQMREPORTING="False" /INSTANCENAME="$(var.InstanceName)" /AGTSVCSTARTUPTYPE="Manual" /ISSVCSTARTUPTYPE="Automatic" /ISSVCACCOUNT="NT AUTHORITY\NetworkService" /ASSVCSTARTUPTYPE="Automatic" /ASCOLLATION="Latin1_General_CI_AS" /ASDATADIR="Data" /ASLOGDIR="Log" /ASBACKUPDIR="Backup" /ASTEMPDIR="Temp" /ASCONFIGDIR="Config" /ASPROVIDERMSOLAP="1" /SQLSVCSTARTUPTYPE="Automatic" /FILESTREAMLEVEL="0" /ENABLERANU="True" /SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS" /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /ADDCURRENTUSERASSQLADMIN="True" /TCPENABLED="0" /NPENABLED="0" /BROWSERSVCSTARTUPTYPE="Disabled" /RSSVCSTARTUPTYPE="Automatic" /RSINSTALLMODE="FilesOnlyMode" /SECURITYMODE=SQL /SAPWD="tomsoN_admin_1032"'
                    UninstallCommand='/Action=Uninstall /INSTANCENAME="$(var.InstanceName)" /FEATURES=SQLENGINE /QUIET="True" /HIDECONSOLE'
                    InstallCondition="NOT SqlInstanceKeyFound"
                    DownloadUrl="https://download.microsoft.com/download/1/5/6/156992E6-F7C7-4E55-833D-249BD2348138/ENU/x64/SQLEXPR_x64_ENU.exe"
                    Name="SQLEXPR_x64_ENU.exe">
            <RemotePayload CertificatePublicKey="B78FE7F6917E1BC5F4A9C77BA3D555A0E807B9E0" CertificateThumbprint="67B1757863E3EFF760EA9EBB02849AF07D3A8080" Description="Microsoft SQL Server 2014  Express SP1" Hash="0C90C147A1C2A550165C9301AE7A6C604E318E51" ProductName="Microsoft SQL Server 2014  Express SP1" Size="318752832" Version="12.1.4100.1" />
        </ExePackage>

    </PackageGroup>