Build and Deploy Databases to an Isolated Development Environment
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 who 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 Team System Database Edition is not installed if you install the following prerequisites:
Microsoft .NET Framework version 3.5 Service Pack 1
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 Database Edition
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.
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:
|
|
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 run-time (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 Databases with Unit Tests.
Related Scenarios
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
Scenario: Manage Changes to Database Server Objects
Scenario: Manage Changes to Database Server Endpoints
See Also
Concepts
Scenario: Create and Modify Database and Server Objects
Terminology Overview of Database Edition