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?

Louvenialouver

I have done this using registry keys I wasn't aware of the WMI recommendation, can you direct me to the information? I can share my registry key version if you like. – Veiled

See blogs.msdn.com/b/sqlexpress/archive/2006/07/29/… – Louvenialouver

That refers to SQL 2005 I guess the same could apply to 2012 but I have been installed all version of SQL Express since MSDE by detecting it via registry keys - it is true that MS change the keys but between major versions I have never seem them change. It might be possible to implement that code in the Extended BA (wixextba.codeplex.com) or the same thing in 3.8 using a BA function. – Veiled

I would be interested in seeing your implementation. I might be able to extend it later to use WMI. – Louvenialouver

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.

Veiled

@Louvenialouver Could you please help me with SQL SERVER 2008 and SQL SERVER 2008 CE @ #19840100 – Petta

This line: <dep:Provides DisplayName="Net2 SQL Server 2012 Express" Key="SQLServer2012Express,$(var.InstanceName)" Version="11.0.3000.0" /> only works if you add WixDependencyExtension.dll to the project, and add xmlns:dep="http://schemas.microsoft.com/wix/DependencyExtension" to the Wix tag. – Reader

For those who want the code to work on x64 machines you must add a pair of RegistrySearchs with Win64="yes" which search on the x64 regsitry – Ansel

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>

Adapter