|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
Build and Deploy Databases to a Staging or Production Environment
Database developers perform individual development tasks, with each developer working in a separate isolated development environment (often referred to as a sandbox). The process for deploying a tested version of a database project into a staging or production environment is similar but has some key differences.
In general, access to staging and production servers is restricted. The servers might contain other databases that you must preserve. Often, the target database already exists, and it might contain data that must also be preserved. You are less likely to want to modify server settings when you deploy a database than when you deploy to a development environment. In some cases, even if you have permission to deploy a database, you might not have permission to update the server settings.
In the deployment properties for your database project, you can configure its settings to match the staging and production server environments. These settings are separate from the other developers' settings for their isolated development environments. By maintaining this separation, you can set up project configurations for your staging and production environments that other developers cannot modify. Each configuration has a target database connection to a staging or production server, its own .sqldeployment file, and its own .sqlcmdvars file.
You might also configure your staging and production settings to prepare the deployment script but skip the actual deployment. By taking this strategy, you can review the deployment script, make changes if necessary, and then manually deploy it into the target environment.
Deployment Configuration Details
Before you deploy your project to a staging or production environment, you should consider the following issues:
You probably want to use the collation of the target database because the staging or production environment is already set up.
You do not want to re-create the database every time because you would lose data.
You might want to deploy database properties if you are deploying a new database. If you are deploying updates to an existing database, you probably do not want to deploy the database properties because they should already be set up correctly.
You probably want to back up the database as part of the deployment process, unless you have already backed up the objects and data as a separate step from the deployment process.
You want to block deployment if data loss might occur because you are updating a database that often contains production data.
You might want to generate DROP statements for objects that are in the database but not in the database project. Your database project should represent the correct version of the staging and production schemas. An exception might be if you must move data manually after you deploy updates to the database. In this case, you do not want to drop the objects until after you migrate the data.
SQL Command Variables
When you deploy to a staging or production environment, the variables should have values that are appropriate for that environment. For example, you might need values for the service brokers or service certificates in your staging or production environments that differ from the values that are in your development environment. By specifying a different .sqlcmdvars file for each target environment, you can avoid having to change the values of those variables when you change deployment targets. This practice also eliminates the requirement to define your .sqlcmdvars file in terms of MSBuild variables to have configuration-specific values. You can have a different .sqlcmdvars file for each configuration that you want to deploy.
A database project can contain definitions for database objects, for server objects, or for both. In most environments, developers can change database objects, but only the database administrator can change server objects. You can enforce this restriction by putting server objects in a separate project (known as a server project). You can then restrict version control so that only your administrators can change the server project. In a staging or production environment, the server project and its objects will most often be deployed separately from the project that contains the database objects.
You deploy a server project by using the same procedures that you use to deploy a schema project.
You can deploy a database project at a command prompt on a computer on which Visual Studio Team System Database Edition is not installed if the following prerequisites are installed:
Microsoft .NET Framework version 3.5 Service Pack 1
SQL Server Management Objects (SMO)
These should be installed on any computer on which SQL Server is installed.
In addition to these prerequisites, you must also transfer the following files to that computer, perhaps by first copying them onto a universal serial bus (USB) drive:
The build output of your database project (debug or retail)
The contents of the Deploy folder for Database Edition
You can typically find this in [Program Files]\VSTSDB\Deploy.
The assemblies for SQL Server Compact Edition
After you install the prerequisites and transfer the files, you can deploy the database project (in the form of the .dbschema file) to a target database.
In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.
Get started with build and deployment: Before you configure, build, and deploy your first database project, you might want to understand how to use database projects in a team environment. You can also gain a better understanding of the build and deployment process. In addition, you can learn about all the properties and settings that you can use to control how your project is built and deployed.
Deploy only completed objects: You can exclude files that contain the definitions for database objects that you are not ready to deploy or test.
Configure your database project for the build process: You can configure settings that control how your database project is built. For example, you can specify the output path.
Configure your database project for deployment:
Build your database project: You can build your database project in Visual Studio or at a command prompt with MSBuild to prepare for deployment.
Deploy your database project: You can deploy your database project in Visual Studio, by using MSBuild, or by using VSDBCMD at a command prompt to update the target database or server.
Troubleshoot problems: You can learn more about how to troubleshoot the most common issues around building and deploying a database project.