[wix-users] Suitability of MSI

[Edwin Castro]

You should be able to do nearly everything you need to do with the Windows
Installer, MSI. You should be able to write custom actions for those things
that the Windows Installer engine doesn't do on it's own. The WiX Toolset
should provide most of the functionality you need that is not provided by
MSI. I'm thinking things like IIS and MS SQL Server database deployments.
That said I would caution against using MSI to install your database.

Databases are rarely installed on the same server where other application
components are installed. Often you install the front end web site and/or
web service APIs to many servers for horizontal scaling. Which of these
servers should the database be installed on? Likely none of them as likely
the database is a different server all together. In fact, the only server
that likely makes sense is the database server itself but your DBAs likely
will not allow that. Instead, you'll have to designate a server that will
have the database MSI installed that will not actually have the database
installed because the database will actually be deployed to another server.
You'll also need to guarantee that this "proxy server" is always the server
used to upgrade the database otherwise it will try to install the database
from scratch as the MSI product was not previously installed on a different
"proxy server".

In my opinion, the Windows Installer is not the best option to manage
datastore deployments. On upgrade you change schema *and* migrate data from
old schemas to new schemas. In complicated deployments you'll want that
process to run outside the context of the Windows Installer. I think the
Windows Installer is a good option to install database deployment tools
which can then be used to manage the database deployment.

The WiX Toolset does provide actions to create empty databases and execute
SQL scripts, inline and script files, which can be useful for very simple
deployments. The Windows Installer and WiX cannot know what actions, if
any, are appropriate for a rollback. You'll need to provide the scripts
that do that work. WiX will just execute them for you.

I'd instead recommend looking into database migration tools instead. My
quick google search found Flyway DB as an example. There were other options
years ago when I used them more regularly, including tools from Microsoft
for MS SQL Server. Some of these tools track changes and can migrate a
database from some snapshot to a future snapshot applying all changes in
between. Some of those tools allow you to undo those changes which could be
useful for rollback scenarios. Other tools work of a model which is
compared to the target database at runtime and the change script is
generated at runtime. Some DBAs do not trust these tools but they generally
rock! Highly recommended! The Microsoft offering used to work this way. Not
sure if it still exists nor how it has changed since 2013 when I last used
it.

--
Edwin G. Castro

On Thu, Feb 6, 2020, 09:14 Ven H via wix-users <
wix-users at lists.wixtoolset.org> wrote:

> I need to take care of the installation / configuration of the following
> features. Is MSI a best fit for all these requirements, especially
> Database?
>
> Prereqs Check (no installation / uninstallation)
> Enable / Disable Features
> Create / Update / Remove Registry Entries
> Register / Unregister COMs (with Heat)
> Register / Unregister GAC (with Heat and XSL transform)
> Set / Remove ACL
> Update IIS Settings (Web Site, Binding, Auth, Default Document, App Pool
> Props, Mime Types etc)
> Create / Remove Event Viewer Categories
> Start / Stop Services
> Add / Remove MSMSQ
> Add / Remove Shortcuts
> Install / Upgrade Databases (including Create / Update tables, Stored
> Procedures, Indexes, Triggers and so on)
> Lots of files (with Heat)
> Manage web.config files
> Custom Actions
>
> HotFix
> Repair
> Upgrade
> Rollback (including DB)
>
> ____________________________________________________________________
> WiX Toolset Users Mailing List provided by FireGiant
> http://www.firegiant.com/
>