I’ll continue the WiX-related discussion as I build the installers that maintain the system run at Skiviez’s new venture Warehouse Fulfillment Services. WiX has been fantastic, and without it, I probably wouldn’t be using the Windows Installer, but I’ve quickly found that having these installation packages makes deploying updates to the production server and much less tense process: for the Web site, the API server, and the background task worker, installing and updating to a newer version of my software is as simple as a double-click.

I also created an installer for the database. This is new territory for me; in the past, well, the database has largely been ignored as far as configuration management was concerned. It usually had grown “organically” over time, and by organically, I mean people adding things as they needed it directly in SQL Server Management Studio. Setting up a test database, to that end, usually meant grabbing a copy of the production database and using it with sensitive data removed. Blech.

Since I was dealing with a new system, I wanted to avoid this situation from the beginning, being able to release updates to the database in a sensible, maintainable way, and ensuring that the database could be installed on a new machine as a turn-key solution should the software ever become valuable enough to be sold. What I wanted was a WiX/MSI installation package that would do the following:

• On the first install, it should create the database and execute SQL scripts that
  • create any new users or logins required by the database;
  • create the schema;
  • create the “version 1.0″ tables, indexes, and foreign key relationships; and
  • populate the database with “required data,” such as those in lookup tables like Core.Countries or Core.AddressTypes.
• On any subsequent upgrade, it should just run SQL patch scripts that make changes to the database.

Now, I could make this simple or I could make this complicated. To keep things simple, I assume that the database versioning progresses linearly: that is, the SQL patch scripts that I add in the future (to add new tables, add indexes, and so on) will be expected to be executed in a certain order. I’m of the opinion that this is a pretty sensible restriction to have on a database.

The way I ended up doing this in WiX was by creating a project with two components. One component contains the database and the scripts that are run to create and populate the virgin database; it only ever runs once. The second component contains the patch scripts; it runs on every install or upgrade.

Wouldn’t having all those patch scripts run every time cause problems, like trying to create tables twice and such? Well, yes, so I’ve made sure to construct the scripts according to a certain pattern. My database has a table called Core.DatabasePatches which contains a GUID and a human-readable description field. Each of my SQL patch scripts, let’s take AddIndexToVariationTypes.sql as an example, is then coded to the following pattern:

BEGIN TRANSACTION;
 
DECLARE @PatchIdentifier UNIQUEIDENTIFIER;
DECLARE @PatchDescription NVARCHAR(255);
 
SET @PatchIdentifier = '{C2929CCB-54C7-4060-8CD5-D606B0BB2C77}';
SET @PatchDescription = 'Adds an index to the Core.VariationTypes table to support bulk import operations.';
 
IF (NOT EXISTS(SELECT * FROM Core.DatabasePatches WHERE PatchIdentifier = @PatchIdentifier))
BEGIN
 CREATE INDEX IX_VariationTypes_VariationTypeName ON
	Core.VariationTypes (VariationTypeName, MerchantId);
 
	INSERT INTO Core.DatabasePatches (
		PatchIdentifier,
		Description
	) VALUES (
		@PatchIdentifier,
		@PatchDescription
	);
END
 
COMMIT TRANSACTION;

You can see that all of the work is wrapped in a big IF statement that says “check the Core.DatabasePatches table to see if this patch has already been applied, and if it has, well don’t do it again.” This way, I can let WiX just run the damn scripts every time without me resorting to storing a “database version” registry key or some other value that I could use as a <condition> on a WiX component. As time goes on, these scripts can get consolidated into the same file and merged back into a “baseline schema” for the next major upgrade, so I’m not too worried about scaling here.

There is still the problem of the component that sets up the database initially trying to run on every install, however. The Installed property isn’t good enough here because that will return false when we are doing updates. So what I ended up doing is creating a new BASELINE_INSTALLED property that checks for the Version that I’ve used in the initial version of my installer.

    <upgrade Id="{SOME-GUID}">
      <upgradeVersion
        Minimum="1.0.0"
        IncludeMinimum="yes"
        OnlyDetect="yes"
        Property="BASELINE_INSTALLED" />
    </upgrade>

Then the component that creates the database has an appropriate <condition> on it:

<component Id="ComponentSqlDatabaseCore" Guid="{SOME-GUID}">
  <condition><![CDATA[NOT BASELINE_INSTALLED]]></condition>
  <createFolder>
    <permission
      GenericAll="yes"
      User="SQLServerMSSQLUser$[ComputerName]$[SQLINSTANCE]" />
  </createFolder>
  <sql:SqlDatabase
    Id="SqlDatabaseCore"
    ConfirmOverwrite="yes"
    ContinueOnError="no"
    CreateOnInstall="yes"
    CreateOnReinstall="no"
    CreateOnUninstall="no"
    Database="Armadillo"
    DropOnInstall="no"
    DropOnReinstall="no"
    DropOnUninstall="no"
    Instance="[SQLINSTANCE]"
    Server="[SQLSERVER]">
    <sql:SqlFileSpec
      Id="SqlFileSpecCore"
      Filename="[ProgramFilesFolder]Skiviez\Armadillo\Database\Armadillo.mdf"
      Name="Armadillo" />
    <sql:SqlLogFileSpec
      Id="SqlLogFileSpecCore"
      Filename="[ProgramFilesFolder]Skiviez\Armadillo\Database\Armadillo.ldf"
      Name="ArmadilloLog" />
    <sql:SqlScript
      Id="SqlScriptCreateDatabaseUsers"
      BinaryKey="BinaryKeyCreateDatabaseUsers"
      ContinueOnError="no"
      ExecuteOnInstall="yes"
      ExecuteOnReinstall="no"
      ExecuteOnUninstall="no"
      Sequence="1" />
    <sql:SqlScript
      Id="SqlScriptCreateCoreSchema"
      BinaryKey="BinaryKeyCreateCoreSchema"
      ContinueOnError="no"
      ExecuteOnInstall="yes"
      ExecuteOnReinstall="no"
      ExecuteOnUninstall="no"
      Sequence="2" />
    <sql:SqlScript
      Id="SqlScriptInsertHarmonizedTariffSystemCodes"
      BinaryKey="BinaryKeyInsertHarmonizedTariffSystemCodes"
      ContinueOnError="no"
      ExecuteOnInstall="yes"
      ExecuteOnReinstall="no"
      ExecuteOnUninstall="no"
      Sequence="3" />
  </sql:SqlDatabase>
</component>

For the sake of completeness, here’s what the component for the patch SQL scripts looks like:

<component Id="ComponentSqlDatabasePatches" Guid="{SOME-GUID}">
  <createFolder />
  <sql:SqlScript
    Id="SqlScriptAddVariationTypeNameIndex"
    BinaryKey="BinaryKeyCreateVariationTypeNameIndex"
    ContinueOnError="no"
    ExecuteOnInstall="yes"
    ExecuteOnReinstall="yes"
    ExecuteOnUninstall="no"
    Sequence="4"
    SqlDb="SqlDatabaseCore" />
</component>

When I have new patches to add in the future, I’ll just add them to that component, using the Sequence property make sure they run in my intended order on a fresh installation.

Now, I don’t have any scripts that un-apply any of these changes on uninstall or rollback, but since this is an installer used by one person in the world (me), I’m keeping that simple for now. Obviously something for a retail application would have that functionality, although I’ve seen recommendations that database management for those would be handled in the application itself, not in a Windows Installer. Caveat emptor!

I also don’t drop the database on uninstall because that seems terrifying to me, particularly if I have a blonde moment and do a major upgrade one day without realizing it.

This could be a totally insane approach, fair warning, but I don’t know of a better way (yet). Hope this helps someone else out there. Happy installing!