WIX: Scheduling a custom database upgrade to happen After=”InstallSqlData” and Before=”StartServices”

At Composable, we develop MSIs for deploying and upgrading instances of the Composable platform. Anytime we want to update an instance, we just simply double click on the latest Composable installer, and we’re all set. The MSI does everything, and no one has to execute any arbitrary scripts or make any ad-hoc changes. The installer takes care of patching of files, upgrading databases, updating xml config files, etc … It took a bunch of work to create this well oiled upgrade machine, and we’re always adding migration steps, but this keeps us in check and customers can always upgrade to the latest version without any work on their end.

We recently had an issue where some our NT services were not running after upgrades. Upon looking at the logs, it appeared the database was not the correct version when the services started up. What’s up with that? Upon further inspection, it appeared our upgrade scripts were actually running after the services already began to start. If the services accessed the database before the database was upgraded, bad things can happen.

In our WIX database section, we have a custom action for looping through all the migration scripts that are necessary to run. The registration of this custom action looks like this:

    <CustomAction Id="CREATE_UPDATE_CADB"
              BinaryKey="customAction"
              DllEntry="CreateOrUpdateDatabase"
              Execute="immediate"
              Return="check"/>
    <InstallExecuteSequence>
      <!-- needs to run after installfinalize to ensure the database component above is created -->
      <Custom Action="CREATE_UPDATE_CADB" After="InstallFinalize">
        <!-- only run when installing cadb-->
        <![CDATA[&DatabaseFeature=3]]>
      </Custom>
    </InstallExecuteSequence>

<CustomAction Id="CREATE_UPDATE_CADB"

BinaryKey="customAction"

DllEntry="CreateOrUpdateDatabase"

Execute="immediate"

Return="check"/>

<![CDATA[&DatabaseFeature=3]]>

</Custom>

</InstallExecuteSequence>

Execute=”immediate” allows the custom action to run on behalf of the current user running the installer. We need to run as the current user because if the connection settings are configured to be using Windows Authentication, we need to execute and connect to the database as the current user. We also have this custom action executing at the sequence: After=”InstallFinalize”. This allows us to execute after things have actually finished executing (like creating the database, saving the files). If we chose a sequence other than InstallFinalize, then the action would have been executed during the pre-stage, and no files, databases, etc .. would have actually been created at that time since we’re in the immediate phase.

There’s many issues with the above code, so let’s go over them.

We shouldn’t be executing this after InstallFinalize. At that point, the installation is basically done. So if there was a failure, the installation would not have been rolled back.
InstallFinalize is too late for the NT services – they’ve already started. We really want it to be before the InstallServices sequence (Before=”InstallServices”).

We ultimately want the execution behavior of the custom database upgrade action to be:

Run after the database has been created
Run before the NT services are started
Use the current users account
Execute during the deferred execution phase

To accommodate all these requirements, the final custom action configuration is the following:

    <CustomAction Id="SET_CREATE_UPDATE_CADB" 
                  Return="check" 
                  Property="CREATE_UPDATE_CADB" 
                  Value="APPLICATIONFOLDER=[APPLICATIONFOLDER];DB_SERVER=[DB_SERVER];DB_NAME=[DB_NAME];CADB_USERNAME=[CADB_USERNAME];CADB_PASSWORD=[CADB_PASSWORD];
                           CADB_INTEGRATED_USER=[CADB_INTEGRATED_USER]" />    
    <!-- need impersonation to make sure script can use current user's windows auth account
     needs to be deferred so it happens when things are really being installed
    -->    
    <CustomAction Id="CREATE_UPDATE_CADB"
              BinaryKey="customAction"
              DllEntry="CreateOrUpdateDatabase"
              Impersonate="yes" 
              Execute="deferred"
              Return="check"                  
    />
    <InstallExecuteSequence>
      <Custom Action="SET_CREATE_UPDATE_CADB" Before="CREATE_UPDATE_CADB">
        <![CDATA[&DatabaseFeature=3]]>
      </Custom>
      <!-- needs to run after installing sql database and permission scripts to ensure the database component above is created -->
      <Custom Action="CREATE_UPDATE_CADB" After="InstallSqlData">
        <!-- only run when installing cadb-->
        <![CDATA[&DatabaseFeature=3]]>
      </Custom>
    </InstallExecuteSequence>

<CustomAction Id="SET_CREATE_UPDATE_CADB"

Return="check"

Property="CREATE_UPDATE_CADB"

Value="APPLICATIONFOLDER=[APPLICATIONFOLDER];DB_SERVER=[DB_SERVER];DB_NAME=[DB_NAME];CADB_USERNAME=[CADB_USERNAME];CADB_PASSWORD=[CADB_PASSWORD];

CADB_INTEGRATED_USER=[CADB_INTEGRATED_USER]" />

<!-- need impersonation to make sure script can use current user's windows auth account

needs to be deferred so it happens when things are really being installed

-->

<CustomAction Id="CREATE_UPDATE_CADB"

BinaryKey="customAction"

DllEntry="CreateOrUpdateDatabase"

Impersonate="yes"

Execute="deferred"

Return="check"

<![CDATA[&DatabaseFeature=3]]>

</Custom>

<![CDATA[&DatabaseFeature=3]]>

</Custom>

</InstallExecuteSequence>

Let’s walk through the updates.

First, we needed the custom action to happen After=’InstallSqlData”. This allows us to run this script after the database has been created. This also happens before the InstallServices and StartServices sequences in WIX, so we’re good there.
The next change was to set Execute=”deferred”. This allows us to actually execute during the installation phase, and not the immediate / test phase. This allows us to have the upgrade scripts on disk so we can access them. During the immediate phase (excluding InstallFinalize), the files would not be on disk yet.
By default, deferred actions will execute as the system account. But we need to execute as the current user. So we set Impersonate=”yes”.
Now that we’re executing in the deferred phase, our custom action can’t access the session information in WIX. Session information is only reserved for the immediate phase. To accommodate this, we have an additional custom action (SET_CREATE_UPDATE_CADB), which runs in the immediate phase, collects all the necessary session variables, and stores them into the custom action properties. The custom action now accesses the properties via session.CustomActionData[“myproperty”], rather than session[“myproperty”]. SET_CREATE_UPDATE_CADB is set to run Before=”CREATE_UPDATE_CADB” so that the variables will be present for the custom action.

Let’s see how the execution order gets finalized in our favorite MSI inspector – Orca.

In Orca, we can see our execution sequence requirements are preserved:

CREATE_UPDATE_CADB happens after InstallSqlData
SET_CREATE_UPDATE_CADB happens before CREATE_UPDATE_CADB
CREATE_UPDATE_CADB happens before InstallServices

Related Posts

Deploying Composable Resources via Powershell

Composable APIs via Powershell

Creating your first Web Service with Composable DataFlow