Creating database packages with MSBuild - Deployment Manager

If you are not using TeamCity, you can create database packages using a Red Gate MSBuild script. To use the script, you must have:

• a SQL Developer Bundle or SQL Toolbelt license
• an Automation license for continuous integration

Example: packaging WidgetDev

In the following example, we will run the Red Gate MSBuild script to package our WidgetDev database for deployment.

For more information on using MSBuild, see: MSBuild reference (Microsoft documentation).

The example has three steps:

1. Downloading and unzipping sqlCI.zip
2. Editing the sqlCI.targets file
3. Running MSBuild

1. Downloading and unzipping sqlCI.zip

1. Download the build script from the Red Gate website:

   

2. Unzip sqlCI.zip to a location on your computer.

   In this example, we are using D:\Database Packages.

sqlCI.zip contains the files required to create database packages. To specify the database you want to package, and where you want the package to be created, you need to edit the sqlCI.targets XML file.

2. Editing the sqlCI.targets file

When you edit sqlCI.targets, you must specify a value for at least the following XML elements:

• <scriptsfolder>

  The location of your database (scripts) folder.

  In this example, we have checked out our database from source control to a local working folder: <scriptsfolder>D:\Databases\WidgetDev</scriptsfolder>

  For information on scripts folders and source control, see:

  • Working with scripts folders (SQL Compare documentation)
  • Worked example: setting up SQL Source Control (SQL Source Control documentation)
• <generateCreationScript>

  Specify <generateCreationScript>true</generateCreationScript> to generate a SQL script to create the database. The creation script is included in the package.

• <packageVersion>

  The version number for the database package.

  In this example, our database is at version 1.0, so we specify <packageVersion>1.0</packageVersion>

  Note that if we specify a version number, we need to update sqlCI.targets when we want to create a package with a different version number.

  If you want to run the build script from a continuous integration server, you can specify a variable. For example, $(build_number) for TeamCity, or $(CCNetLabel) for CruiseControl.NET.

• <packageId>

  The name (or ID) for the database package.

  In this example, we specify <packageId>WidgetDevDatabase</packageId>

If you specify a value for only these four elements, when you run MSBuild the package will be created in the folder where you unzipped sqlCI.zip.

In our example, we want to publish the package to a feed. To do this, we also specify values for:

• <packageRepository>

  The URL of your NuGet package feed.

  In this example, we specify <packageRepository>http://localhost:62759/nuget</packageRepository>

  For information on creating package feeds, see: Hosting your own NuGet feeds (NuGet documentation).

• <apiKeyForPackagePublish>

  The API key for your package feed.

  In this example, we specify <apiKeyForPackagePublish>apikey</apiKeyForPackagePublish>

3. Running MSBuild

Now we have specified the required values in sqlCI.targets, we can run MSBuild to package our database.

You can run MSBuild from a continuous integration server (for example Team Foundation Server, CruiseControl.NET, or Jenkins), or from the command line.

To run MSBuild from the command line:

1. In Windows Start menu, click All Programs.
2. In the Microsoft Visual Studio folder, under Visual Studio Tools, click to run the Visual Studio Command Prompt.
3. From the command prompt window, navigate to the folder where you unzipped sqlCI.zip.

   In our example, this is D:\Database Packages.

4. Run msbuild.

   MSBuild runs, creates the package, and publishes it to the package feed.

Deploying the package

To deploy the package we have created, we need to specify some variables in Deployment Manager.