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

Related Scenarios

• Starting Team Development of Databases
  Describes how you create an offline representation of a database schema in a database project and add it to version control.

• Starting Team Development of Databases that Reference Other Databases
  Describes how you can create an offline representation of a database schema, define references to other databases, specify values for variables for target deployment environments, and add the project to version control.

• Starting Team Development of Databases that Reference SQLCLR Objects
  Describes how you create an offline representation of a database schema, define references to assemblies that contain SQL common language runtime (CLR) objects, define database objects that reference those SQLCLR objects, and add the project to version control.

• Starting Team Development of Databases that Use XML Schema Collections
  Describes how you create an offline representation of a database schema, define references to an XML schema definition (XSD) file, define an XML schema collection that uses that file, define columns that use the XML schema collection, and add the project to version control.

• Starting Team Development of Databases that Reference Shared Server Objects
  Describes how you create an offline representation of a database schema, define references to a shared server project, add references to objects that are defined in the server project, and add the database project to version control.

• Build and Deploy Databases to an Isolated Development Environment
  Describes how you can build and deploy your database to your isolated development environment. You can test your changes in isolation before you check them in to version control to share them with your team. You should test your changes in isolation before you build and deploy changes into a staging or production environment.

See Also

Concepts

Writing and Changing Database Code

Creating and Modify Database and Server Objects