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.