Architectural Overview of Database Edition

Visual Studio Team System Database Edition offers many of the same tools, features, and functionality to database developers as Visual Studio Team System offers to other members of an application-development team. For example, you can use the same version control system and other life cycle tools for your database as software developers use for their source code. Because the architecture of Database Edition is so closely integrated with Visual Studio Team System, you can more easily manage the process of database development while improving coordination with those who develop other aspects of the application.

Not only is the core architecture of Team Edition for Database Professionals integrated with the rest of Visual Studio Team System, but also the interface is consistent across components. You work with database projects in many of the same ways as you work with other types of projects in Visual Studio. Database-specific templates appear in the New Project and Add Project Item dialog boxes. In addition, you can perform actions (such as Build and Deploy) and configure settings (such as target database connection and database collation) that are designed specifically for database development.

Database projects appear in Solution Explorer as peers to existing project types. You can put these projects under version control, add and remove project items, and build them with MSBuild.

Each database project is an offline representation of a database and is located in the structure of a Visual Studio solution. You must build and deploy changes to a database project for them to affect an active instance of Microsoft SQL Server 2000, Microsoft SQL Server 2005, or Microsoft SQL Server 2008.

Database Life Cycle Infrastructure

The database life cycle infrastructure provides a variety of services between the database project and the logical representation (model) of the database. This infrastructure includes fundamental features that are required for any database project. These features are collectively referred to as the database project foundation. Features that build on that foundation are referred to as database project features.

Database Project Foundation

The database project foundation includes the following core functions:

Project and project item parsing

The foundation parses database projects and database project items, such as tables, views, and stored procedures. By doing this, it extracts the information about the database objects from the script files that make up the database project. For example, this functionality applies when you import database objects from existing database scripts. Database projects are composed of collections of Transact-SQL (T-SQL) scripts. The database project system interprets these scripts so that you can interact with them as logical schema objects.

Import Database Schema

By using Import Database Schema, you can bring an existing database into Database Edition and put it under version control. Even in an environment where the database is managed under version control, changes might still occur on an active database server that you must bring back to your database project. You can use Schema Compare to import changes from a database server into a database project that is under development.

Tracking dependencies between database objects

The database project system automatically tracks dependencies between database objects. By tracking the dependencies between objects, you make sure that the correct objects are built and deployed. If you introduce a breaking change into your product, you discover that it is a breaking change when you first save the change. Typically, you discover breaking changes when you try to build, deploy, or run a database unit test. Database refactoring uses those dependencies to identify required changes, and data generation can show you the dependency relationships as part of your data generation plan.

Database refactoring

Database refactoring identifies dependencies between database objects, and uses that information to accurately propagate changes throughout the database and unit testing projects.

Creating build scripts for new and existing deployments

Build scripts are used to create a database or update an existing database to match the schema in the database project. Build scripts are composed of pre-deployment scripts, database object creation scripts, and post-deployment scripts. If you deploy to an existing database, at deploy time, the database project is compared with the current schema of the target database. An update script is generated to update the target database using a mix of ALTER, CREATE, and DROP statements.

Validating builds

This functionality includes validating the build steps for pre-deployment scripts, scripts that create or update database objects, and post-deployment scripts, and then reporting those results to you.

Database Project Features

In addition to the core services that are provided for database projects, additional features are built on top of the database project foundation. These features include the following:

Comparing database schemas

Provides the ability to compare two database schemas, whether those are present in a current version of a database project, an earlier version of a database project that is stored in version control, or on one or more active databases. You can also generate the necessary scripts to make the target database match the source database.

Comparing database data

Provides the ability to compare the data in two databases if you have active databases with matching schemas. You can also generate the necessary scripts to make the target database match the source database.

Database unit testing

Automatically generates unit tests for a stored procedure or a function, and supports manual creation of unit tests that make up any valid Transact-SQL. By using this service, you can verify that changes introduced elsewhere do not break your work. As part of a unit test, you can automatically build and deploy your database and generate reproducible test data to establish a consistent starting state. This service also includes a public API that you can use to write custom asserts for adding reusable validation logic for your unit tests.

Generating data

Provides the ability to populate a database with realistic test data that is not just a copy of production data, which might contain confidential information. You can also create custom data generators. This service also includes a public API for writing custom data generators for use with CLR data types, user-defined types, and user-defined data types, or to use custom data generation algorithms for built-in types.

Authoring and executing database scripts

Provides a Transact-SQL editor in which you can create, analyze, and execute database scripts similar to how you might use Query Analyzer or SQL Server Management Studio. This is the same editor that you use to modify your scripts in your database project. Because it is the same editor, it provides a consistent user interface, whether you are working with an offline database project or directly executing scripts against a live database server.

Some of the activities that you perform regularly must be able to be run as part of an automated process. To support this scenario, you can build your database project, deploy it, and run unit tests from the command line, which can include generating representative and predictable test data.