How to prevent database drop during Wix Major upgrade

Question

I am building an installer with Wix 3.10 that will install files, create a service as well as create and populate a database.

I am using the Wix sql:SqlDatabase element to create the database and run some sql scripts to populate it during installation (based heavily on WIX database deployment, installation)

As recommended in the Wix documentation, I am testing a mock upgrade before releasing the initial installer. From what I can tell sticking to major upgrades is strongly recommended and so I am using the MajorUpgrade element as per the examples.

Unfortunately however during the major upgrade I can't seem to prevent Wix from uninstalling the database, nor can I find any guidance on how to handle this. I understand that a major upgrade is effectively an uninstall of the current version followed by a fresh install of the new version, but surely there is a way of retaining parts of the original?

I have a similar problem with the service that I install too, but based on this SO question Wix Major Upgrade: how do I prevent Windows service reinstallation? the solution appears to be to add a condition to the delete service entry of the install sequence:

<InstallExecuteSequence>
    <DeleteServices>NOT UPGRADINGPRODUCTCODE</DeleteServices>
</InstallExecuteSequence>

This implies to me that it is possible to retain entries across a major upgrade, but I may be misunderstanding.

Unfortunately there does not appear to be any equivalent installexecute sequence element for a SqlDatabase entity. Is there any guidance on how one should approach this?

UPDATE

Based on PhilDW's answer, changing the sequence or schedule of the major upgrade is done by changing the Schedule attribue:

<MajorUpgrade 
  DowngradeErrorMessage="A newer version of [ProductName] is already installed."
  Schedule="afterInstallExecute"/>

Note however that this will only take you so far - if you plan to add support for trusted authentication as well as SQL authentication in your installer (as per the SO article above) it will not work, my assumption is that Wix determines that one component was never installed (whichever authentication option was not chosen) and therefore will always drop the database.

Answer 1

There are a few ways to address this, depending on the internals of your MSI:

1. A major upgrade that is sequenced at the "end", after InstallExecute and just before InstallFinalize, means that the upgrade is basically an install of the new product on top of the currently installed product. File overwrite rules apply, one of which is that data files won't be replaced if they have been updated after installation. So data files are saved. Other considerations are that binary file versions must be updated for those you need updating, and component rules must be followed.

2. If the issue is based on custom actions that run when the older product is being uninstalled, then you can use a custom action condition such as REMOVE="ALL" and not UPGRADINGPRODUCTCODE. UPGRADINGPRODUCTCODE is set in the older product as it is being uninstalled, not in the incoming upgrade.

3. I believe some WiX util types custom actions are based on the uninstall of the related component, so you wouldn't need that condition in 2. A major upgrade after InstallExecute increments the ref counts of each component (which is why you need to follow component rules) while following file overwrite rules. So your data file would have its ref count counted up to 2, would not be overwritten, then the older product uninstall would count it down to 1, so that the component remains, and an uninstall custom action based on component removal would not run.

4. If there is something you need to do in the upgrading install that's custom action based, then WIX_UPGRADE_DETECTED will tell you that you are upgrading an installed product.

On the upgrade link you refer to, Chris Painter's answer is the correct one. It's basically the same point that I'm making here, so of course he's correct :)