Installing a SQL database with WIX
I like to share a sample about how to install a Sql database using a custom action shipped within WIX, it is an easy task and it can be useful in many scenarios.
Let's take a look to this source file "sql.wxs":
<Product Name='SQL app 1.0' Id='DB501C18-86C7-4D14-AEC0-86416A69ABDE' Language='1033' Codepage='1252'
Version='1.0.0' Manufacturer='Cibrax Ltd.'>
<Package Id='????????-????-????-????-????????????' Keywords='Installer' Description="SQL App 1.0 Installer"
Comments='SQL app is a registered trademark of Cibrax Ltd.' Manufacturer='Cibrax Ltd.' InstallerVersion='100'
Languages='1033' Compressed='yes' SummaryCodepage='1252' />
<Media Id='1' Cabinet='Sample.cab' EmbedCab='yes' />
<User Id="MySQLUser" Name="[SQLUSER]" Password="[SQLUSERPASSWORD]"></User>
<Directory Id='TARGETDIR' Name='SourceDir'>
<Directory Id='ProgramFilesFolder' Name='PFiles'>
<Directory Id='INSTALLDIR' Name='TestSQL'>
<Component Id="MySqlComponent" Guid="C50999A0-02FD-42d5-9F65-7375318DD328">
<SqlDatabase Id="MySqlDatabase" Database="MyDatabase" Server="[SQLSERVER]" Instance="[SQLINSTANCE]"
CreateOnInstall="yes" DropOnUninstall="yes" User="MySQLUser" ContinueOnError="yes">
<SqlScript Id="CreateTables" ExecuteOnInstall="yes" BinaryKey="CreateTablesBin"></SqlScript>
</SqlDatabase>
</Component>
</Directory>
</Directory>
</Directory>
<Binary Id="CreateTablesBin" src="CreateTables.sql"></Binary>
<Feature Id='Complete' Level='1' Description="Full" Title="Full Installation">
<ComponentRef Id='MySqlComponent' />
</Feature>
</Product>
</Wix>
I'll start describing the most important elements in this script:
<User>
It contains authentication settings necessary to sign on against the Sql database server.
As you can see, the user and password are properties, so you can change them later in runtime maybe using a user dialog or custom action. Other point to consider, you can nest this element within the component, in that case, the installer will create a windows user account user during the install, and we don't need that in this sample.
<SqlDatabase>
Using this element we can specify some settings required to create the database, such as database name, server, instance and user. The user attribute contains a reference to the existing user element, in this case, "MySQLUser".
It supports other conditional attributes, such as CreateOnInstall, DropUnistall and ContinueOnError. These attributes specifies what to do during the install, uninstall and when an error occurs.
The properties will take default values if you do not provided them, for example, the default value for Server is “localhost”.
<SqlScript>
I will use this element to execute a sql script because the previous element only creates an empty database, but in this sample, I also want to create some empty tables within the database.
The BinaryKey contains a reference to the script file in the Binary table.
The CreateTables.sql file is a simple script to create a new table, and looks like this:
[Test_Id] [int] NOT NULL
) ON [PRIMARY]
At this point, we have a simple wix script to install a new database, but what is next? Obviously, we need to compile this file using "candle.exe" and then, "light.exe".
We will start compiling this script in a wix object file using "candle.exe" ( Replace "[Wix Path]" with the current wix path in your machine ):
If everything goes fine, you will able to find a sql.wixobj file in the same folder than sql.wxs.
As next step, we will use "light.exe" to create the final msi file.
Ughh, you will get a wix linker error after running this command, but what is wrong with the script? Well, to install a sql database, wix uses some custom actions, which will run along the msi, and we did not specify any of these custom actions in our script.
Do we need to include these custom actions in our script? Not exactly, "light.exe" can include them automatically when the msi is generated.
To do that, we need to provide some extra parameters:
After running this command, you will find a new sql.msi file. Now, you can use it to install your database in any machine.