Share via

Build and Deploy Databases to a Staging or Production Environment

  • Article

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 with 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.

Configuring Your Database Project for Deployment to a Staging or Production Environment

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.

Deploying Server Projects

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.

Deploying Roles

The roles that you use in your database must be deployed to all servers to which you deploy that database. When you deploy a database to a staging or production server, you must define all required users and associate those users with their appropriate roles.

Command-Line Deployment

You can deploy a database project at a command prompt on a computer on which Visual Studio Premium 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 Visual Studio Premium

    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.

Common Tasks

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.

Task

Supporting Topics

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:

  • You can configure deployment for any build configuration by associating it with a target database, a file that contains deployment details, and a file that contains variables that affect deployment.

  • You can control the details of the deployment by modifying the file that contains deployment details.

  • You can customize variables to use different service brokers or service certificates in each deployment.

  • You can control whether the database properties are updated when you deploy the database and what values those properties should have.

Populate reference or look-up tables: You can add reference data to tables when you deploy your database project. You might choose to do this for tables that contain data that changes infrequently, such as shipper information.

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 as part of a database unit test run, or by using VSDBCMD at a command prompt to update the target database or server.

In addition, you can also deploy by using Team Foundation Build if you define a custom workflow.

Troubleshoot problems: You can learn more about how to troubleshoot the most common issues around building and deploying a database project.

See Also

Concepts

Writing and Changing Database Code

Creating and Modify Database and Server Objects