An Overview of Database Creation and Deployment in a Team Environment
With Team Edition for Database Professionals, your development team can manage database change by using a database project that is under version control. If you perform the database administrator (DBA) role for your team, you create that project, import the schema from a production server, configure database settings, and establish a plan for generating test data. When you are ready to share the project and its settings with the rest of the team, you put files that are associated with the project under version control.
If you are a developer or a tester who is working on the database, you check out the current version of the database schema from the version control system and then make your changes in an isolated development environment, or sandbox. You can then test those changes in the isolated environment without affecting other members of the team. After changes are complete, you check the files back into the version-control system so that they can be built and deployed to a test server. Other team members can pick up your changes by synchronizing to the version that you checked into version control.
Setting up a Database Project
In a team environment, if you perform the DBA role, you set up the project for the team, and then each team member can work in their own isolated development environment using the same project. To set up a project, you typically perform the following sequence of actions:
You create the database project. You typically perform this step in conjunction with the next one.
You establish the schema for the database project. Most development work starts from an existing database. In that case, you can import the schema from that database into the database project. You must have access to the source database (often a production database) for this step. If you are creating a database, you might need to work with the database architect to develop the initial database schema.
You set properties on the database project that control build and deployment. This step includes setting the default collation, setting the build output path, specifying connection options, and setting the target database name.
You check the database project and its contents into version control so that your team can access them.
You define a data generation plan to allow each team member to work with realistic test data that contains no sensitive information. You add the plan to the database project and check it into version control.
You also define database unit tests for existing database functionality. You typically include unit tests in a separate project in the same solution as the database project and check the entire solution into version control.
Now the team can start development work.
Performing Iterative Development in an Isolated Development Environment
As a developer, you synchronize your local development environment with the version control system. You customize the Target connection property on the database project to point to the server where you want to host your copy of the database. Feature work consists of the following process:
You identify the task to perform. This step might involve identifying work items that are assigned to you in Team Foundation work item tracking, for example.
You modify the database project, its contents, and any related source code. You check the files out of version control, where you can keep both your code and the database project synchronized.
If necessary, you create or modify unit tests and update the data generation plan.
You build and deploy the database project and related application or applications to the server where you have your private copy of the database.
You run unit tests, possibly using Data Generator to create test data.
You repeat steps 2 through 5 until all tests pass and you are satisfied with the results.
You check in all changes to your database project, application, and unit tests when unit tests pass and you are satisfied with the quality of the work.
You move on to the next task.
By following this process, you can develop and test your changes in isolation, where they cannot affect other developers before the changes reach a consistent level of quality. At that point, you check your work into version control and allow other developers to access the improvements that you have made.
Deploying Changes into Production
After the team has made all necessary changes, the next step is to update the production server. If you are responsible for performing that task, you can get the most recent version of the database project from version control, build the deployment script, manually update that script as necessary, and then run the script to deploy the schema changes into production.
Some version control software supports the ability to label a set of files as they exist at a particular time. For example, you can label a database project, application source code, unit tests, and other files as they existed for a particular release. Even if development continues from that point, you can always retrieve the specific versions of files that the release comprises. For more information about how to deploy a previously labeled version of your database project, see How to: Deploy an Older Version of a Version-controlled Database.
Representations of your Database
If you follow this process of database development, you can have up to three representations of your database:
The database server representation, which contains the database and the data. If you are a database developer, you will primarily work with a development or test database. Many organizations have a separate database administrator role that has access to the production database.
The database project, which is the offline representation of the database schema. Database projects also include any data generation plans that you use to generate test data and scripts that you use to deploy and manage your database. For more information, see An Overview of Database Projects.
The version-control repository, which tracks all changes that you or any member of the team makes to the database project.
The database server exchanges data with the database project, which also exchanges data with the version-control repository. You can manage your database more effectively if you understand how these representations exchange data with respect to version control, schema importation, and deployment.
See Also
Tasks
Walkthrough: Creating an Isolated Database Development Environment
Concepts
Terminology Overview of Database Edition
Other Resources
Getting Started with Database Projects
Building and Deploying Database Schemas
Working with Database Projects in a Team Environment
Walkthroughs (Creating and Updating Version-Controlled Database Schemas)