WiX and Databases

Since WiX is still fairly new and is an Open Source project, don’t be surprised if you hear a lot of noise about the little or no documentation available .

In this and the coming few posts I am going to start posting some of the challenges/ roadblocks I’ve hit and the workarounds or solutions I could find.

 

WiX and Databases

Let’s look at how to perform some simple tasks with databases first and then try the more complex ones.

First thing to get started would be to add a WiX SQLExtension and UtilExtension. To use any extension with WiX you will have to include a reference to the schema within the WiX element.

xmlns:sql="http://schemas.microsoft.com/wix/SqlExtension" xmlns:util="http://schemas.microsoft.com/wix/UtilExtension"

If you are using Votive the Visual Studio plugin to build your project then make sure you add references to WixSqlExtension.dll and WixUtilExtension.dll from your WiX binaries directory. If you are using a batch file (like I do) for building your project then pass these extensions to both Candle and Light i.e. -ext WixSqlExtension -ext WixUtilExtension

 

Create a SQL User:

 <util:User Id="MySQLUser" Name ="[SQLUser]" Password="[SQLPassword]"/>

 

Create a SQL Database:

<sql:SqlDatabase Id="MySqlDatabase" Database="MyDatabase" Server="[SQLSERVER]" Instance="SQLEXPRESS"  CreateOnInstall="yes" DropOnUninstall="yes" User="MySQLUser" ContinueOnError="yes"/>

Create a SQL Database and run some custom sqlscripts:

<sql:SqlDatabase Id="MySqlDatabase" Database="MyDatabase" Server="[SQLSERVER]" Instance="SQLEXPRESS"  CreateOnInstall="yes" DropOnUninstall="yes" User="MySQLUser" ContinueOnError="yes"/>
<sql:SqlScript Id="MySQLScript1" ContinueOnError="no" ExecuteOnReinstall="yes" ExecuteOnInstall="yes" BinaryKey="ScriptBin1"/>        <sql:SqlScript Id="MySQLScript2" ContinueOnError="no" ExecuteOnUninstall="yes" ExecuteOnReinstall="no" ExecuteOnInstall="no" BinaryKey=" ScriptBin2"/> 
</sql:SqlDatabase>

 Create a Database and specify where to place the database files on the target machine:

<sql:SqlDatabase Id="MySqlDatabase" Database="MyDatabase" Server="[SQLSERVER]" Instance="SQLEXPRESS"   CreateOnInstall="yes" DropOnUninstall="yes" User="MySQLUser" ContinueOnError="yes">        
<sql:SqlFileSpec Id="Mdf" Name=" MyDatabase " Filename=" ="[TARGETDIR]MyDatabase.mdf" Size="2MB" GrowthSize="2MB"/>        
<sql:SqlLogFileSpec Id="Ldf"  Name=" MyDatabase _log"  Filename="[TARGETDIR] MyDatabase _log.ldf" /></sql:SqlDatabase>

 

Restore a database from an mdf/ldf from WiX

Recently I had to restore a database from an mdf/ldf from WiX, sounds pretty straightforward right? WiX actually does pretty well to make it straightforward (only if you knew the magic words).

Since I spent a few man hours on a workaround with what I had and did manage to avoid using VBScripts, I am going to post that here too; here is what I did:

So all we know until now is how to create a new database and run custom sql scripts, what we need to do is to create a database at a user specified location and restore the database from a mdf/ldf which is on the CD(final product).

You can create the database at the user specified location as shown above. You can also move the mdf/ldf to a location that the user desires.  The problem comes when you want to do an attach command from a sql script. How does your sql script know where the mdf/ldf were moved to?

 

1) To begin with,  I manually created a .bak file from the mdf/ldf and copied that to the project folder.

 

2)  Then I went ahead and created a new database from WiX at the location where the user desired and also specified where to place the mdf/ldf on the target machine (and the names) as shown in the last example above.

 

3) And then finally ran this sql script from WiX to search for the physical location (filename) of the mdf/ldf that was created from WiX and then replace them using a Restore command.

 

/*
Written by: Sajo Jacob. Lasted Modified: November 08, 2007
NOTE: This script depends on the .BAK file generated from the MDF
*/

CREATE PROCEDURE RestoreDB
as
DECLARE @restoredb varchar(1024)
DECLARE @path varchar(1024)
BEGIN

SET @path= MyDatabaseBak'
SET @restoredb = 'RESTORE DATABASE MySqlDatabase' + ' FROM DISK = ' + '''' + @path +''''
+ ' WITH REPLACE,RECOVERY,MOVE ' + ''' MyDatabase ''' +
'  TO ' + '''' + ( select top 1 filename from master.dbo.sysaltfiles where name = 'MyDatabase') + '''' +
', MOVE ' + ''' MyDatabase_Log''' +
' TO ' + '''' + ( select top 1 filename from master.dbo.sysaltfiles where name = 'MyDatabase_log') + ''''
EXEC (@restoredb)
END

WiX support for doing something like this is by letting you write Inline sql statements by using the SQL attribute on SqlString element

<sql:SqlString Id=" Attach " SqlDb="MySqlDatabase" ExecuteOnInstall ="yes" ContinueOnError="no" Sequence="1" SQL="CREATE DATABASE $(var.LongDBName) ON (FILENAME=N'[TARGETDIR]MyDatabase.mdf'), (FILENAME=N'[TARGETDIR]MyDatabase_log.ldf') FOR ATTACH" /> 

If you have a readonly mdf/ldf then a restore can result in a readonly database, so assuming that is the case here, let’s perform a set operation after the restore. Sequence attribute here helps with the order of execution.

<sql:SqlString Id="ReadWriteDatabase" SqlDb=" MySqlDatabase " ExecuteOnInstall="yes" ContinueOnError="yes" Sequence="2" SQL="ALTER DATABASE $(var.LongDBName) SET READ_WRITE WITH NO_WAIT" /> 

WiX and Windows Authentication

Just take off the User attribute in the SqlDatabase element we had earlier in the database creation section and it will use Windows Authentication.

About

I love coffee!

Posted in Programming, Windows Installer XML Tagged with: , , ,
0 comments