VSTS4DBP deployment using MSBuild

Deploying a database project to multiple environments can be challenging. Especially when all target machines are not equal; that is, their files are not stored in the same folder, the various database or server objects differ.

Visual Studio provides essential features that makes deployment works like a breeze: configurations and MSBuild.

One configuration for each environment (DEV, Test, Production)

By using Visual Studio configuration manager, you can define specific configurations for your solution. Simply right-click on your solution and select "Configuration manager" from the contextual menu.

Once Configuration Manager is displayed, you can see that two default configurations are created:
– Debug: this is the default configuration
– Release: this configuration is (or can be) used for deployment of VS projects

Configurations used for database deployment may be different than those used for software deployment. Simply create as configurations as necessary. For example, in my solutions, I always a configuration named "Development" for my own machine, "Test" for test server and "Production" for the production server.

<Add configuration manager picture>

Once configurations are defined, we can set my DBPro project accordingly.

Some files contain project's properties. They are located just below the project:

If we bring the project's properties screen (right-click project and select properties), we can create or update files as needed by the selected configuration. A properties file can be used by several configurations.
for different server and database objects. 

Name	Description	Project's properties usage
Database.sqlcmdvars	Contains SQLCMD variables.	Deploy tab
Database.sqldeployment	Contains deployment specific parameters like whether or not the database should always be re-created, if we want the database to be backed-up before deployment, etc.	Deploy tab
Database.sqlpermissions	Contains specific Grant or Deny options on database objects.	–
Database.sqlsettings	Contains information's on database settings like database collation, recovery model, etc.	Project settings tab

Deploying the project

MSBuild enables us to deploy the project by using the command line. To be able to use MSBuild easily, open the visual studio command prompt from the start menu–> Visual Studio 2008:

Here is a common syntax that can be used to deploy a database project:

MsBuild <database project> /t:Build

So, the resulting command line is:

MsBuild DB_ETL_DEMO.dbproj /t:Build

Now the project has beeen built. A DB_ETL_DEMO.dbschema file has been created. We can now deploy the dbschema file to our target database. The command line syntax is:

MsBuild DB_ETL_DEMO.dbproj /t:Deploy /p:Configuration="Test"

The file db_etl_demo.sql is now created in the test folder of my project:

Find out more MSBuild command line options

Refer to "An Overview of Database Build and Deployment" in DBPro documentation. It contain lots of sample to use MSBuild in conjunction with DBPro.

Summary

This article demonstrated how a database project can be deployed using Visual Studio configurations and MSBuild command line. MSBuild is installed by Visual Studio and is very convenient when DBPro projects have to be deployed to servers or machine where the database does not have similar setup. Also, since this is a command line tool, it can be wrapped into a scheduled task in order to automate database deployment at specific intervals. In another article, I will talk about TFSBuild, the build feature that comes with Team Foundation Server.

Labels: VSDB