THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Building a database installer with WiX, datadude and Visual Studio 2010

Today I have been using Windows Installer XML (WiX) to build an installer (.msi file) that would install a SQL Server database on a server of my choosing; the source code for that database lives in datadude (a tool which you may know by one of quite a few other names). The basis for this work was a most excellent blog post by Duke Kamstra entitled Implementing a WIX installer that calls the GDR version of VSDBCMD.EXE which coves the delicate intricacies of doing this – particularly how to call Vsdbcmd.exe in a CustomAction. Unfortunately there are a couple of things wrong with Duke’s post:

  1. Searching for “datadude wix” didn’t turn it up in the first page of search results and hence it took me a long time to find it. And I knew that it existed. If someone else were after a post on using WiX with datadude its likely that they would never have come across Duke’s post and that would be a great shame because its the definitive post on the matter.
  2. It was written in October 2009 and had not been updated for Visual Studio 2010.

Well, this blog post is an attempt to solve those problems. Hopefully I’ve solved the first one just by following a few of my blogging SEO tips while writing this blog post, in the rest of it I will explain how I took Duke’s code and updated it to work in Visual Studio 2010.

If you need to build a database installer using WiX, datadude and Visual Studio 2010 then you still need to follow Duke’s blog post so go and do that now. Below are the amendments that I made that enabled the project to get built in Visual Studio 2010:

  1. In VS2010 datadude’s output files have changed from being called Database.<suffix> to <ProjectName>_Database.<suffix>. Duke’s code was referencing the old file name formats.
  2. Duke used $(var.SolutionDir) and relative paths to point to datadude artefacts I have replaced these with Votive Project References http://wix.sourceforge.net/manual-wix3/votive_project_references.htm
  3. I commented out all references to MicrosoftSqlTypesDbschema in DatabaseArtifacts.wxi. I don't think this is produced in VS2010 (I may be wrong about that but it wasn't in the output from my project)
  4. Similarly I commented out component MicrosoftSqlTypesDbschema in VsdbcmdArtifacts.wxi. It wasn't where Duke's code said it should have been so am assuming/hoping it isn't needed.
  5. Duke's ?define block to work out appropriate SrcArchPath actually wasn't working for me (i.e. <?if $(var.Platform)=x64 ?> was evaluating to false)  so I just took out the conditional stuff and declared the path explicitly to the “Program Files (x86)” path. The old code is still there though if you need to put it back.
  6. None of the <RegistrySearch> stuff is needed for VS2010 - so I commented it all out!
  7. Changed to use /manifest option rather than /model option on vsdbcmd.exe command-line. Personal preference is all!
  8. Added a new component in order to bundle along the vsdbcmd.exe.config file
  9. Made the install of the Custom Action dependent on the relevant feature being selected for install. This one is actually really important – deselecting the database feature for installation does not, by default, stop the CustomAction from executing and so would cause an error - so that scenario needs to be catered for

I have made my amended solution available for download at: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.zip It contains two projects: the WiX project and the datadude project that is the source to be deployed (for demo purposes it only contains one table).

image

I have also made the .msi available although in order that it gets through file blockers I changed the name from InstallMyDatabase.msi to InstallMyDatabase.ms_ – simply rename the file back once you have downloaded it from: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.ms%5E_ .You can try it out for yourself – the only thing it does is dump the files into %Program Files%\MyDatabase and uses them to install a database onto a server of your choosing with a name of your choosing - no damaging side-affects. I will caveat this by saying “it works on my machine” and, not having access to a plethora of different machines, I haven’t tested it anywhere else. One potential issue that I know of is that Vsdbcmd.exe has a dependency on SQL Server CE although if you have SQL Server tools or Visual Studio installed you should be fine. Unfortunately its not possible to bundle along the SQL Server CE installer in the .msi because Windows will not allow you to call one installer from inside another – the recommended way to get around this problem is to build a bootstrapper to bundle the whole lot together but doing that is outside the scope of this blog post. If you discover any other issues then please let me know.

Here are the screenshots from the installer:

image

image

image

image

image

image

And once installed….

image

Hope this is useful!

@jamiet 

Published Thursday, February 10, 2011 10:45 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SQLChap said:

I do like using Wix and I do like automated DB installs and we do use them together in the office for some projects.

My issue with this approach is that information about what is installed is recorded on the machine that the installer is run on. It would be better if it was recorded on the SQL Server instance perhaps in the master DB where it could be interogated with queries.

The primary requirement for a MSI is a installation folder, for databases it should be a server instance.

Uninstallation is also an issue, it is possible to run SQL on uninstall but the SQL Server might not be available at that time. Because of the above disconnect between the database items and the MSI information you could end up stuck with no database and no way to complete the uninstall.

February 11, 2011 3:19 AM
 

jamiet said:

SQLChap,

Good point. Maybe Contained Databases are a step in that direction: http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx

JT

February 11, 2011 3:52 AM
 

SQLChap said:

Perhaps, but I'd suggest the issue is more the other way around. Rather than decoupling the database from the DB Engine, we need a way of decoupling the installation of tha database and database objects with the underlying operating system.

Windows Installer works fine for the first installation of a system but I'd suggest it's not so good for ongoing upgrades of systems. The typical uninstall / re-install used for upgrading applications is not so easy to implement for a database.

February 21, 2011 7:11 AM
 

Peter Schott said:

Jamie,

Could this be used for upgrades to DBs as well? Not all that familiar with Wix, but if we could build the appropriate configurations and schemas into an upgrader package, I can see use for this. If it's just to create a DB for the first time, it probably isn't quite as useful. We're looking for an easy-ish way to package up versions to move through our environments and this looks at least a little promising. Thanks for sharing your findings with us.

February 22, 2011 7:05 PM
 

jamiet said:

Hi Peter,

It uses Vsdbcmd which is basically what datadude uses when it does deploys (not strictly true but think of it as true for the purpose of this comment).

So in short, yes, it can be used for upgrades. If you can do it with datadude then you can do it with this deployment mechanism.

Regards

JT

February 23, 2011 2:27 AM
 

James Simmonds said:

Jamie - if you are building WiX packages then check out WiX# - it's a .net wrapper around the WiX system. The bottom line is that you write familiar .Net code and it will translate this into WiX/Msi for you - it even takes are of custom actions very well. Used it on a previous project and it worked very well, even with a quite complex installer.

http://www.csscript.net/WixSharp.html

http://www.codeproject.com/KB/install/WixSharpArticle.aspx

March 8, 2011 2:28 AM
 

Evan Bosscher said:

A couple of suggestions for how this might be improved for flexibility and portability:

1. In the DatabaseArtifacts.wxi file use $(var.MyDatabase.Configuration) instead of hard coding the debug folder. Typically when it comes time for me to deploy I change my configuration to "Release", and this way it will work for both

2. The text for x64 didn't work for me either in VS2010, but rather than hard-coding the "Program Files (x86)" consider using $(env.ProgramFiles)

May 3, 2011 4:38 PM
 

Evan Bosscher said:

In order to get this to deploy properly on a machine that doesn't have datadude installed you have to add another file component to the VsdbcmdArtifacts.wxi file:

<Component Id="MicrosoftDataSchemaUtilities" Guid="{YOUR-GUID-HERE}">

<File Id="Microsoft.Data.Schema.Utilities.dll" Source="$(var.SrcArchPath)Microsoft.Data.Schema.Utilities.dll" KeyPath="yes" Checksum="yes"/>

</Component>

Otherwise the deployment will explode

May 6, 2011 11:40 AM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM
 

Paul Taylor said:

Has anyone found a way to get the VSDBCMD output to write to the MSI log correctly? Running VSDBCMD via console gives info like:

Creating [dbo].[NewTableName]...

but this is what is written to the logfile:

Creating ....

February 20, 2012 8:13 AM
 

James Braun said:

I run the msi but get a message saying Setup Wizard ended prematurley

Is there a log file that I can read?

February 21, 2012 10:13 AM
 

Ron Jennings said:

One additional item that is needed if you want to be able to run the installer on a Windows 2008 server is the vsdbcmd.exe.config file. This contains the directives necessary to allow mixed mode assemblies to be executed under .NET 4.0.

May 8, 2012 9:22 AM
 

jamiet said:

Ah OK. Thanks Ron.

May 8, 2012 9:26 AM
 

Rajesh said:

Hi,

When I use the Wix to deploy the database, its throw the Setup Wizard ended prematurley message end of installation. And I have checked its not installed.

Can you help me to resolve this problem.

May 15, 2012 7:53 AM
 

Rajesh said:

Hi,

I have checked the Msi lob file, it shows the error message like

CAQuietExec:  *** The target database schema provider could not be determined.  Deployment cannot continue.

CAQuietExec:  Error 0x80070001: Command line returned an error.

CAQuietExec:  Error 0x80070001: CAQuietExec Failed

CustomAction LaunchVsdbcmd returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)

Action ended 13:41:30: InstallFinalize. Return value 3.

But, at that time of installation its create folder in programsfolder and copies all files to that location.

Can you help me to resolve this issues.

Advance thanks.

May 16, 2012 4:15 AM
 

Peter Schott said:

Rajesh, that first line looks very much like the error I got when I tried to deploy to SQL Server 2012. DB Projects don't work against SQL 2012 - you'll need to upgrade to SSDT SQL Projects in order to continue.  (We're about to go through that on our stuff to see what works and what doesn't.)

Can you deploy your project outside of WiX?  Are you using SQL 2012 as your target?

May 16, 2012 10:20 AM
 

Rajesh said:

Hi,

Thanks for reply.

I am using SQL 2008 R2. I can able to deploy the DB project outside Wix.

Can you help me to fix the issue.

May 17, 2012 12:41 AM
 

Krish said:

Hi,

I have download the tools from this site and ran in my local machine (which has SQL server installed). I have received the error as

CAQuietExec:  Error 0x80070001: Command line returned an error.

CAQuietExec:  Error 0x80070001: CAQuietExec Failed

CustomAction LaunchVsdbcmd returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)

Action ended 13:41:30: InstallFinalize. Return value 3.

I tried the resolution details from google, can't find any resolution.

Can you help me to fix the issues.

Thanks

May 17, 2012 4:53 AM
 

Peter Schott said:

Rajesh - not sure what to do if it's working when you do a normal deploy. The message you're getting just looked like the one I got when trying to deploy to SQL 2012. :(  I haven't actually worked through this process myself and we're actually prepping to upgrade our projects to SSDT/2012 projects to be ready for an upgrade later this year. Reading through the errors would lead me to lean towards a deploy problem, but if you can deploy through command line and/or through VS, I'm not sure what to do.

May 17, 2012 12:47 PM
 

Zangetsu said:

Has anyone tried to drop the database using this installer? What needs to be done to drop the database using uninstall?

May 24, 2013 11:17 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement