Working with database packages - Deployment Manager

You can deploy SQL Server databases with Deployment Manager using database packages.

This article describes:

What database packages contain

Database packages contain database object creation scripts representing the state of a database (including any static data). Packages can also contain:

Upgrade scripts to upgrade a database from previous versions you have deployed with Deployment Manager
Creation scripts to create a database
HTML documentation of the database

How Deployment Manager upgrades databases

Deployment Manager upgrades a database in one of two ways:

Dynamic upgrade (using SQL Compare during deployment)
If you deploy a database package that does not contain a relevant upgrade script, Deployment Manager upgrades the database automatically, using the SQL Compare engine.

Dynamic upgrades are useful when you are deploying to development or testing environments.
Static upgrade (using an upgrade script)
If you deploy a database package that contains a relevant upgrade script, Deployment Manager runs the script to upgrade the database.

Static upgrades are useful when you are deploying to staging or production environments, because a build can validate the deployment and report any errors in the build log.

Deployment validation

Deployment Manager can validate a deployment by checking that the state of the database you are deploying to is identical to the state of the database in the package.

There are two types of validation:

Pre-deployment validation
Deployment Manager validates the deployment before it is performed.

Pre-deployment validation is useful because it can detect database drift. Database drift occurs when changes are made to the database outside of a Deployment Manager deployment. If Deployment Manager performs a dynamic upgrade, these changes will be lost. If Deployment Manager performs a static upgrade, there may be errors when the upgrade script is executed.

If database drift is detected, the validation fails. The database is not updated, and the next deployment steps are not performed.
Post-deployment validation
Deployment Manager validates the deployment after it has been performed.

Post-deployment validation is useful because it can confirm that the deployment was successful. The validation will also detect any changes made to the database schema or static data outside of the Deployment Manager deployment (if a DDL or DML trigger ran during the deployment, for example).

If the deployment was not successful, or any unexpected changes are detected, the validation fails. The database has been updated, but the next deployment steps are not performed.

Extended properties

When you first deploy a database with Deployment Manager, two extended properties are added to the database:

DeploymentManager Deployed Package ID
The name, or ID, of the database package.
DeploymentManager Deployed Package Version
The version number of the database package.

Deployment Manager uses these extended properties in future pre-deployment validations and static upgrades.

In future pre-deployment validations, the extended properties will be used to ensure that the database is validated against the correct package version.

In future static upgrades, the extended properties will be used to ensure that the correct upgrade script is used during the deployment.

The extended properties are updated automatically with each deployment.

Note: You cannot deploy SQL Azure databases with Deployment Manager because SQL Azure does not support extended properties.