Build and Deploy Databases to an Isolated Development Environment

  • Article

In an isolated development environment (often referred to as a sandbox), you and your teammates can update, build, and deploy database code without introducing changes that conflict with each other. After you generate test data and run database unit tests, you can deploy your version of the code to a staging or test server, where your version merges with code from other developers. Before you deploy a database project, you configure its properties based on whether you will deploy it to an isolated development environment instead of a staging or production server.

Configuring Your Database Project for Local Deployment

When you configure deployment properties, you can identify a target database connection, a .deploymentconfig file, and a .sqlcmdvars file that are specific to your isolated development environment. In the deployment properties for your database project, you can configure the settings for your isolated development environment to differ from the deployment settings that are stored in the database project. Therefore, you can provide a target database connection to your isolated development database, a different .deploymentconfig file, and a different .sqlcmdvars file.

Deployment Configuration Details

Before you deploy your project to an isolated development environment, you should address the following issues:

  • You probably want to use the collation of the database project because, in most cases, your development environment should match your target staging or production environments.

  • You might want to always re-create the database because you have no production data that you must preserve.

  • You want to deploy database properties because you want to set up your development environment to match the target staging or production environments.

  • You do not want to back up the database because this practice increases the overhead for your deployment and offers no benefit if you re-create the database every time that you deploy.

  • You might not want to block deployment if data loss might occur because you are generating test data instead of working with production data. If you do not expect the changes that you are making to cause data loss, you might want to block deployment to test that expectation.

  • You do not want to generate DROP statements for objects that are in the database but not in the database project. You might have temporarily excluded objects from the database project that are incomplete, or you might have prototypes of objects in the target database that you want to preserve.

SQL Command Variables

When you deploy to an isolated development environment, you want the variables to have values that are appropriate for that environment. For example, you might need values for the service brokers or service certificates in your development environment that differ from the values in your staging or production environments. By specifying a different .sqlcmdvars file for your isolated development 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

When you deploy a database project, you deploy the objects and settings for the database. When you deploy a server project, you deploy the objects that are defined in the master database, but server settings are not changed. Server settings can be ignored, or you can verify that the values for some or all of the settings on the target server match the values that you specify in your server project.

Other than the difference in how settings are handled, you deploy a server project by using the same procedures that you use to deploy a database project.

Deploying Roles

You must deploy the roles that you use in your database to all servers to which you deploy the database. However, in your development environment, you can limit the number of users you must associate with each role. Typically, you want at least one user for each role for testing, but you do not have to deploy a full set of users to your isolated development environment.

Command-Line Deployment

You can deploy a database project from a command prompt on a computer on which Visual Studio is not installed if you install the following prerequisites:

  • Microsoft .NET Framework version 4

  • SQL Server Management Objects (SMO)

    These objects 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

    This folder is typically found 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.

Reference Data

You can add statements to the post-deployment script in your database project to populate one or more tables with reference data. For more information, see Add Reference Data to Tables When You Deploy the Database.

You can also compare your deployed database to a database that contains reference data if you want to synchronize one or more tables. For more information, see Compare and Synchronize Data in One or More Tables with Data in a Reference 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 you use database projects in a team environment. You can also improve your understanding of the build and deployment process and learn about all the properties and settings that control how your project is built and deployed.

Learn by doing: By completing the introductory walkthroughs, you can quickly learn about the whole process, from creating an isolated development environment through establishing a baseline and performing iterative development.

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 project for build: You can configure settings that control how you build your database project. For example, you can specify that warnings should be treated as errors.

Configure your project for deployment:

  • You can configure deployment for any build configuration by associating a configuration 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 (.deploymentconfig).

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

  • You can control whether the database and server properties are updated when you deploy the project 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 from a command prompt with MSBuild. You can configure the maximum number of errors or warnings and suppress warnings if you want to ignore them. You can perform actions as part of the build, either just before or just after the database project is built.

Deploy your database project: You can deploy your database project in Visual Studio, by using MSBuild, or by using the command-line tool VSDBCMD to update the target database or server. Before you check in changes to your database project, you might also want to test whether a full team build and deployment is successful.

Troubleshoot problems: You can learn more about how to troubleshoot the most common build and deployment issues, such as issues when you rename SQL common language runtime (CLR) projects and assemblies.

Next Steps

Before you check in your changes, you should consider running database unit tests to verify whether your database is working as intended. You can write additional tests to verify any code that you have written. Optionally, you can deploy the project and generate test data when you run the tests. For more information, see Verifying Database Code by Using Unit Tests.

Build and Deploy Databases to a Staging or Production Environment

Starting Team Development of Databases

Starting Team Development of Databases that Reference Other Databases

Starting Team Development of Databases that Reference Shared Server Objects

Starting Team Development of Databases that Reference SQLCLR Objects

See Also

Concepts

Creating and Modify Database and Server Objects

Starting Team Database Development