Defining Deployment Properties

By: Armando Prato

Overview

We have now created the project and examined/defined some database options. Now we will see how we can control the behavior of our create and refactor deployments.

Explanation

Double clicking the Database.sqldeployment file in the Solution Explorer presents the following screen:

This is a very important screen. From here, you can tailor your deployments based on your needs. You can choose to backup any existing database prior to deployment to keep a good copy in case the deployment fails due to some unforseen error. If you don't care about the database contents, you can choose to just re-create the database everytime it's deployed. There are a slew of advanced options that can control deployment behavior such as dropping objects that are in an existing database but aren't part of your database project.

Two options I'd like to point out are the drop down for Deployment comparison collation and the option to Deploy database properties.

If you're going to deploy to a SQL Server that has a collation that differs from the collation defined for your project, the default "Use the collation of my project" will deploy the database and it's objects using your defined collation. This is an important point. You could end up with a database whose collation doesn't match the SQL Server instance collation. There's another option in the drop down to use the collation of the server which will cause the deployment to deploy objects using the collation of the target SQL Server.

Recall in the previous slide that I mentioned that when deploying your database that database options defined in your project will override any options set manually after deployment? You can choose to prevent this by choosing to uncheck Deploy database properties. If you choose to do this, a new database will be created using SQL Server defaults. Any non-default options can be accounted for in a post deployment script.