The solution I am talking about in this blog posting can be downloaded here: http://blogs.kungfucoder.com/downloadmaterials/dbdeployment.rar
You will need to download and install Wix (at least Version 3.5 RTM newer).
I created a sample solution to start as a conversation point for the database deployment; (please feel free to comment, trash, or otherwise use). This is patterned somewhat after the way we deploy things (although I don’t generally provide the MSI files and such as part of the deployment to customers – but maybe that is something I should consider). What I tried to create here is a full example of building and deploying a database using the AdventureWorks sample database.
I tried to keep it as clear and minimal, yet functional as I could. It also includes some of the other difficulties I had when I first started using Wix, as far as the pre-reqs, I actually include a bootstrapper in mine that installs other things like the .net framework, etc because it is bundled as a package (I left the pre-reqs in for the moment).
The installer generates the upgrade script (which for new databases includes a database-create), and executes it, the call to the VSDBCMD could easily be modified to only generate the resulting script (in fact, if you look In the resulting database installer directory, you will see the script is generated, and then run from within VSDBCMD).
This MSI solution allows you to deploy the database to basically any computer. I use this type of installer when I deploy to my internal test environments that run as part of the TFS Lab Management Environments. The same installer can be used to deploy as part of a client install as well. By using VSDBCMD, the deployer can either create a new database everytime, or upgrade a database in-place.
One thing that is probably worth mentioning is that some of the other team I work with do not include a database server inside their lab, instead that have a beefy database server and they assign database names with a version number, like “AdventureWorks_1_88_7381_0” for example, and the script then restores a well-know database, and applies the installer to it.
When I deploy this to a TFS Lab Environment, I generally include it in a batch file, this is because I copy all of the installation materials locally to the computers in the lab, and from there execute the installers. However, the MSI could be run directly from the Deployment tab in the DefaultLabTemplate (VS2010) of the wizard that simply passes the desired parameters for the server name (and optionally the database name).
The Deployment tab could contain something as simple as this:
Msiexec /I $(BuildLocation)\Install.AdventureWorks.msi DATABASESERVER=SomeServer\SomeInstance DATABASENAME=AdventureWorksCustomName
Solution Contents
AdventureWorksLT2008 – Database project reverse engineered from the SQL 2008 Adventure Works Download (from here: http://msftdbprodsamples.codeplex.com/releases/view/37109)
Fragment.AdventureWorks – This install component contains the files that are associated with the actual database
Fragment.Support.DatabaseInstaller – this install component contain the files that are associated with VSDBCMD; I included the supporting files needed when the computer that the resulting MSI is executed on does not have the SQL management studio installed
Install.AdventureWorks – this is the actually installer that uses the two fragments to generate a database installer
Shared – this contains the various binaries, including the dbSchemas for 2005 and 2008 (I am still looking for one for 2008R2).
This is what the project looks like this when you open it (It is not bound to any version control):
Installer
The installer takes two parameters currently:
DATABASESERVER – Name of the Database Server (and Instance) to install on
DATABASENAME – Name of the resulting database
I defaulted them both as . for the server name (local) and the database name of AdventureWorks.
This installer assumes that the user running it has enough rights on the database server to create a new database if needed; that is, the installer the way it is does not require that the database be pre-created.
You can run this with parameters with a command line something like this:
Msiexec /I Install.AdventureWorks.msi DATABASESERVER=SomeServer\SomeInstance DATABASENAME=AdventureWorksCustomName
I could easily add a UI to this if you want, as well as some other options.
After installing, you get this in the control panel.
If you uninstall, it only removes this entry from the control panel, it does not do anything to the database, this is by design, I don’t think you want an installer to remove a database that could potentially have customer data in it.
Wix Fragments
The WiX Fragments within this solution basically only carry the files needed for the installer. I chose to break the installer up to make it more modular and clear to those reading this Blog. Fragment.AdventureWorks takes the files that are generated from the AdventureWorksLT2008 database project and incorporates them into a single Component that can be used within an installer.
When I create these fragments, I try to choose Ids that are unique enough to indicate the database being carried, this is because often I will need to install more than one database, so the installer can actually reference and deliver multiple databases if needed.
Of particular note, it is important to select the 'Bind Files into the library file'; this ensures that the consumer of the Wix Library will have the files needed/referenced.
Fragment.SupportDatabaseInstaller carries with it all of the supporting files needed for VSDBCMD. I chose to copy the files into the Shared project so that they are both versioned, and do no rely on the build computer to have everything installed in specific locations. Generally speaking I also include any other needed restribitutables this way as well; most of my work is specific client-based work so this is generally not an issue. The Support.DatabaseInstaller fragment contains two components, one for the Installer files and one for the SQL CE Runtime.
The goal of this installer was to provide enough of the supporting files so that the database could be installed from a computer that does not have the SQL Management tools installed on it.
Installer
The Wix Installer ended up actually being fairly simple.
Definition of parameters that can be applied to the MSI to control where the database is installed, the parameter names are DATABASESERVER and DATABNAME; included are the custom actions that copy the parameter to local variables inside the WiX Installer. (you could opt to use the parameters directly, but I tend to like the parameters and properties like this).
<!--
**************************************************************
Command Line Parameters, and Actions to Retrieve and User them
**************************************************************
-->
<!-- Command Line Parameter: APPSERVER HOST NAME -->
<Property Id="DATABASESERVER" Value="." Secure="yes"/>
<Property Id="DATABASENAME" Value="AdventureWorks" Secure="yes"/>
<CustomAction Id='SetTargetDatabaseServer' Property='TargetDatabaseServer' Value='[DATABASESERVER]' />
<CustomAction Id='SetTargetDatabaseName' Property='TargetDatabaseName' Value='[DATABASENAME]' />
Install/Uninstall Actions, on installation we want to run the database installer VSDBCMD.
<!--
**************************************************************
Install/Uninstall Actions
**************************************************************
-->
<!--Define the custom action to build the vsdbcmd.exe command line string-->
<CustomAction Id="SetLaunchVSDBCMDCommandLine"
Property="LaunchVsdbcmd"
Value=""[#VSDBCMD.exe]" /a:Deploy /cs:"Server=[TargetDatabaseServer];Integrated Security=true;" /dsp:Sql /dd+ /manifest:"[#db.AW_MANIFEST]" /p:TargetDatabase="[TargetDatabaseName]""
Execute="immediate"/>
<!--Define the custom action to execute vsdbcmd.exe-->
<CustomAction Id="LaunchVsdbcmd" BinaryKey="WixCA" DllEntry="CAQuietExec" Execute="deferred" Return="check" Impersonate="yes"/>
Of particular note is the VSDBCMD command line:
/dsp:Sql indicates that we will be using the SQL provider for the installation
/dd+ indicates that the database should be modifed (there are options to only create the sql required to perform the action)
/manifest - I confess, the installer really should have been in the fragment, because here we actually are referencing the AW_MANIFEST file contained inside the Fragment.AdventureWorks
<!-- FROM the Fragment.AdventureWorks project -->
<Component Id="db.manifest.AW" Guid="*">
<File Id="db.AW_MANIFEST" Source="$(var.DatabaseScript_PATH)\$(var.AdventureWorksLT2008.ProjectName).deploymanifest" KeyPath="yes"/>
</Component>
Notice also that I specify the TargetDatabaseServer and the TargetDatabaseName on the command line of VSDBCMD.
Finally, the definition of the execution sequence of the custom actions
<!--
**************************************************************
Define the Execute Sequence for all of the custom actions
**************************************************************
-->
<!-- Install/Uninstall Actions -->
<InstallExecuteSequence>
<Custom Action='SetTargetDatabaseServer' After='LaunchConditions' />
<Custom Action='SetTargetDatabaseName' After='LaunchConditions' />
<Custom Action="SetLaunchVSDBCMDCommandLine" Before="InstallFiles"/>
<Custom Action="LaunchVsdbcmd" After="InstallFiles" >
<![CDATA[&ProductFeature=3 AND NOT REMOVE]]> <!--Only run this custom action if a certain feature is being installed--> <!-- NO Action on Uninstall -->
</Custom>
</InstallExecuteSequence>
Notice, that we will only launch VSBDCMD if ProductFeature is being installed. Product Feature is defined as follows inside the installer. As you can use, this specific Feature contains the Database, along with the Installer and supporting files.
<!--
**************************************************************
Product Feature Definitions
**************************************************************
-->
<Feature Id="ProductFeature" Title="AdventureWorks Database" Level="1">
<ComponentGroupRef Id="Database_AW" />
<ComponentGroupRef Id="Database_Installer" />
<ComponentGroupRef Id="Database_Installer_Support" />
</Feature>
This is not the perfect way to implement this solution, but by breaking things apart, hopefully how this all works will become clearer to those who need to install databases using WiX. I recently downloaded the SSDT from Microsoft, I believe by the time this gets posted, CTP4 will be widely available. This is the next generation of Database Project support for Visual Studio, and is support in both VS2010 and dev11. It looks like the VSDBCMD.exe is being replaced by a new set of command line utilities. I'll post another entry in the future that has the same level of detail using the newer technology.
Subscribe