Architectural Overview of Team Edition for Database Professionals
Visual Studio Team Edition for Database Professionals 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 Team Edition for Database Professionals is so closely integrated with Team System, you can more easily manage the process of database development while improving coordination with those who develop other aspects of the application.
For more information about the capabilities of Team Edition for Database Professionals, see Managing Database Change.
Visual Studio Integration
Not only is the core architecture of Team Edition for Database Professionals integrated with the rest of 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 the 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.
Architectural Composition
Visual Studio Team Edition for Database Professionals includes the following architectural areas:
Database Project
Design-time Validation Database
Database Lifecycle Infrastructure
Each database project is an offline representation of a database and exists within 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 or Microsoft SQL Server 2005.
The design database is a local instance of SQL Server is used to perform design-time validation of your database project.
Database Lifecycle Infrastructure
The database lifecycle infrastructure provides a variety of services between the database project and the design 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. In doing so, it extracts the information about the database objects from within 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 you can interact with them as logical schema objects.
- Import Database Schema
By using Import Database Schema, you can bring an existing database into Visual Studio Team Edition for Database Professionals and put it under version control. Even in an environment where the database is managed under version control, changes may 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 ensure 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 build 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 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:
- 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 comprise 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 edit 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
Command Line Support
Some of the activities that you perform on a regular basis need to 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.
See Also
Concepts
Managing Database Change
Extensibility in Team Edition for Database Professionals
Terminology Overview of Team Edition for Database Professionals
How do I Team Edition for Database Professionals
Terminology Overview of Team Edition for Database Professionals