Click here to monitor SSC

Red Gate Support Engineer

Installing databases using Wix

Published 6 November 2008 10:07 am
In the promised second half of my Wix installment, I am going to demonstrate how you can install a SQL Server database as part of a software deployment using Wix and SQL Packager from Red Gate.

In my experience, your average software company has exactly one “Installer Guy”. Typically, this is a very unappreciated fellow who does tons of tedious work to effectively jimmy components onto a user’s computer using the blunt instrument known as Windows Installer. If there was a “hot or not” scale of development sexiness, you would put “Installer Guy” at the definitive “not” end of the scale. But I digress.

One of the many things Windows Installer can’t do is to put a SQL database on someone’s computer, and given the number of programs being shipped that have a SQL Server back-end database, this is darn shame. How to accomplish this herculean task has been approached in many different ways by many third-parties, and Wix even has an extension that will get an installer to create databases and run rudimentary SQL.

However, there is more to database deployment than just pushing a SQL script into an installer, there is the maintainability factor, or how to seperate the development of the SQL from the development of the installer code. There is the recovery factor, or which SQL to run if the installer fails. Finally, there is the upgrade factor, or how to ship database upgrade scripts with the conventional component upgrades such as files and assemblies.

Considering the above factors, SQL Packager can solve a lot of the SQL maintanence challenges by wrapping the scripts to create or upgrade database into a single program that will run the database creation or upgrade scripts when executed. Wix allows you to build “Custom Actions” that include the ability to run console-based programs and even suppress the output from them.

For my example, I have created a database package using SQL Packager for the version 1.0 of my sample product “Packager Test”, called SQLPackagerBaseline.exe. I want to run this silently, and I can accomplish this using the wixca library that comes with Wix, which provides a function called CAQuietExec. In the InstallExecuteSequence table, we add these custom actions, their sequencing, and the condition which will cause them to execute. In this example, for instance, a database is only installed if this is a new installation, and if the user specified SQL authentication, the properties are set appropriately.

<!– Add wixca.dll into the Binary table –>
<Binary Id=wixca src=$(env.WIXROOT)wixca.dll />
<!– Deferred custom actions can’t access properties, so they have to be set using a custom action –>

<

CustomAction Id=Database.Install.Properties Property=Database.Install Value=&quot;[SQLPACKAGEFOLDER]SQLPackagerBaseline.exe&quot; /server:[SERVERNAME] /database:[DATABASENAME] /quiet /makedatabase />
<
CustomAction Id=Database.Install.Properties.Sql Property=Database.Install Value=&quot;[SQLPACKAGEFOLDER]SQLPackagerBaseline.exe&quot; /server:[SERVERNAME] /database:[DATABASENAME] /username:[DBUSERNAME] /password:[PASSWORD] /quiet /makedatabase />
<!– The custom action itself –>
<
CustomAction Id=Database.Install Impersonate=yes BinaryKey=wixca DllEntry=CAQuietExec Execute=deferred Return=check />
<!– Our custom tasks run in the InstallExecuteSequence –>
<!– install and uninstall conditions included –>
<!–
(componentname=2 means to run only on uninstall) –>
<!–
(componentname&gt;2 means to run only on install) –>

<

InstallExecuteSequence>
<
RemoveExistingProducts After=Database.Drop>$PackagerTest.Packages=2</RemoveExistingProducts>
<
Custom Action=Database.Drop.Properties Before=RemoveFiles>$PackagerTest.Packages=2</Custom>
<
Custom Action=Database.Drop After=Database.Drop.Properties>$PackagerTest.Packages=2</Custom>
<
Custom Action=Database.Install.Properties After=InstallFiles>$PackagerTest.Packages&gt;2</Custom>
<
Custom Action=Database.Install.Properties.Sql After=Database.Install.Properties>$PackagerTest.Packages&gt;2 AND USEINTEGRATEDSECURITY=0</Custom>
<
Custom Action=Database.Install After=Database.Install.Properties.Sql>$PackagerTest.Packages&gt;2 AND USEINTEGRATEDSECURITY=1</Custom>
</
InstallExecuteSequence>


The second major bit of work installing a database is to build a user interface. Wix comes with several vanilla user interfaces, such as Wix_Mondo, that you can use without having to design anything yourself. Unfortunately, there is no set of dialogs that you can use to specify the properties of a database, and you may find, like I have, that it’s better to hand-code the whole user interface yourself to get exactly the kind of dialogs that you want. In my example, for instance, the “user name” and “password” edit boxes are grayed-out when Integrated Security is used. I have also included my own banner to brand my installation with.

One thing that is not documented too well is that the coordinates for placing controls on an Installer dialog are in “installer units” and nobody on planet Earth knows how many pixels correspond to an installer unit. For instance, the dialog below is 370×220 “Installer Units”, but the banner is 494×59 pixels in size.



Something that you can do in Wix that you cannot do in Visual Studio Setup and Deployment is to write custom action progress messages to the progress bar. The only way to make the progress bar move, unfortunately, is to code this into your custom action. But at least your user will know what the installer is doing if your database package takes a long time to run!

<
ProgressText Action=Database.Install>Installing database &quot;[DATABASENAME]&quot;</ProgressText>

I have highlighted a lot of the more interesting points of deploying a database as part of a product installation based on Wix and Windows Installer technologies, but there is so much more going on, that it wouldn’t fit into a simple blog. In fact I think I can see you dozing off already!

If you want to see the complete Wix project, including the user interface dialogs and SQL Package file, please download the zip file at the end of this post and feel free to probe and dissect the wix code. Hopefully this sample makes it easier for someone to install a database as part of a Windows product installation.

5 Responses to “Installing databases using Wix”

  1. ToUB says:

    Brian,

    Thanks for writing an article on a topic on which there is not much documentation available. More articles like this geared towards beginners would be appreciated.

    I am a DBA with limited C# or dll writing knowledge (and new to WIX). In trying to create a MSI package for our product that includes Sql Server 2005 Express, I read and re-read you article a bunch of times. But I still do not have a complete idea on how to approach this. I downloaded and played with your sample code, but it doesn’t work on my machine.

    I need to install Sql Server with our *.ini file and then create database, users, tables, SP, UDF, Triggers….. and the whole process. With the documentation available from WIX software, I was able to create and install a Sample Package. But installing Sql Server seems difficult to accomplish.

    Any help would be great.

    http://www.sqlservercentral.com/Forums/Topic760350-146-1.aspx?Update=1

    thanks,
    _UB

  2. vijay200797 says:

    Dear Sir
    i am using following code to make data base installer UI . but i can do this
    pls…. Help Me. my email id :- ram@orientwebsolutions.com

    I Shall be always thankful to u……….

    Language="1033" Version="1.0.0.0" Manufacturer="DATABASE"
    UpgradeCode="83158824-a977-4198-96b7-b9c7bde989e0">






    CreateOnInstall='yes' DropOnUninstall='yes' ContinueOnError='yes'>






  3. rajesh.k says:

    Hi,
    I have created DB MSI file and I have successfully installed. When I update the DB schemas and compile and recreated MSI.

    When I try to run the updated MSI, it will not run fully. Because its not asked at that time of DB details (like server name, user name etc.,).
    Can you please help me to resolve this problem.

  4. robertoosaile says:

    Where are the zip file ?

Leave a Reply