Managing Database Change

If you develop and maintain databases at the enterprise level, your responsibilities probably include coordinating schedules and changes with software-development teams to create unified database applications. You can significantly reduce the effort that this coordination requires by adopting tools and processes that are as similar as possible to those of the software developers who are in your organization.

As many teams move to more iterative, rapid-development paradigms for writing code (such as the Agile method), both software and database developers have an even greater need for tools such as version control. These tools can reduce the risks that are associated with team-based development. By using similar tools and processes, you can gain significant benefits not only for the databases that you manage but also for the integration points between those databases and the software that interacts with them.

How Visual Studio will help

Visual Studio helps you coordinate with your software developer counterparts by encouraging team members to take a more holistic view and focus on the application behavior, instead of the implementation details. This approach diverges from the model where the production database (and its data) are viewed as the center of the application universe. Visual Studio provides a solution set that is targeted at the complete application — both data and logic. To build an application that performs well, is easy to maintain, and can be delivered as quickly as possible, development organizations at the enterprise level must take this inclusive view of the application.

Visual Studio provides features that are targeted at the database developer and database administrator roles of today. For example, Visual Studio supports the concept of an isolated development environment for each database professional. Team members can work on schema-related activities without causing hardship to other team members or the production environment. The long-term vision adds features for the database architect role and expands features for database administrators.

High-level Goals

The database features of Visual Studio have the following high-level goals:

• Deliver a foundation that is based on integrated change management for database applications.

• Provide tools that help you manage database projects.

• Provide tools that help you understand differences.

• Provide tools that help you make changes and assess their effects.

• Provide tools that help you work in an isolated environment to reduce the risks that are associated with change.

• Provide tools that help you test updated solutions.

• Provide tools that simplify the deployment of changes to a database.

• Provide tools that facilitate collaborative development of complex databases.

High-level Tasks

To support these goals, Visual Studio helps you perform the following high-level tasks:

• Create and deploy a new database under version control.

• Put an existing database schema under version control by using the database project. The database project schema is created by importing the schema from a database or existing scripts.

• Modify an offline representation of a database and then deploy the changes.

• Compare schemas or data between databases.

• Develop and run unit tests against database objects.

• Generate predictable, representative test data without compromising sensitive production data.

• Rename database tables, columns, views, or other objects and propagate those changes to the rest of the database project, which includes associated unit tests, views, stored procedures, triggers, and so on.

• Create, edit, validate, execute, and analyze scripts and queries.

The Database Development Lifecycle

Visual Studio supports a process that works together with the new tools. You can think of this process as the database development lifecycle, which provides each database professional with an isolated development environment in which to work and test changes. After changes have been tested, team members share changes through the version control system. The database development lifecycle contains the following phases:

• Establish the project environment

• Perform isolated, iterative development

• Build the project

• Deploy from the project environment

Establish the Project Environment

The following illustration shows how the team member who performs the role of the database administrator (DBA) establishes the project environment:

The DBA starts by creating a database project and importing the database schema from the production database. The DBA then can create a data generation plan to create test data for the isolated development environments. Finally, the DBA checks the database project into version control, which makes it available to the team.

Perform Isolated, Iterative Development

The following diagram shows how team members perform iterative development work in an isolated environment:

Each database professional synchronizes their development environment to version control. They can check out files as they are changed and develop and test those changes in isolation. Changes made to their copy of the database project are deployed only to their isolated development environment. After a team member generates realistic test data and runs unit tests against a private copy of the database, the team member checks the changes into version control. Other team members obtain tested changes from version control.

Generate Daily Builds of the Project

The following illustration shows how the build cycle works for database projects:

The daily build is generated by synchronizing to the most recent tested version of the database project in the version control system. That build can be deployed to a test database where unit tests are run against generated test data.

Deployment from the Project Environment

The following diagram shows how the DBA can deploy a version of the database project into production:

When it is time to deploy a version of the database to production, the DBA synchronizes to a label from the version control system. The DBA retrieves matching files for the database project, related scripts, and tests. Next, the DBA builds the .dbschema file. From the .dbschema file, the DBA generates the deployment script, makes manual modifications as needed, and does a test deployment to the staging server. The DBA iterates this process until the script is ready to be deployed to the production server. If the DBA made changes to the schema or to the pre-deployment or post-deployment scripts, those changes will be checked back in to version control.

See Also

Concepts

Architectural Overview of the Database Features of Visual Studio

Starting Team Database Development