Architectural Overview of the Database Features of Visual Studio
Visual Studio offers many of the same tools, features, and functionality to database developers as it 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 Visual Studio is so closely integrated with Visual Studio Team Foundation Server, 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 that support database development in Visual Studio, see Managing Database Change.
Visual Studio Integration
Not only is the core architecture of Visual Studio integrated with Visual Studio Team Foundation Server, but also the interface is consistent across components. You work with database projects , server projects, and Data-tier Application 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.
Architectural Composition
Visual Studio includes the following architectural areas:
Database Project
Database Life Cycle Infrastructure
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 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 project artifacts 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 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 Visual Studio 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. You can use the Schema Dependency Viewer to view and navigate the dependencies between database objects. 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. Database refactoring is only available in Visual Studio Premium and Visual Studio Ultimate.Creating deployment scripts for new and existing deployments
Deployment scripts are used to create a database or update an existing database to match the schema in the database project. Deployment 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 structure and relationships of the objects in your database project and validating the syntax of the pre 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 the compiled .dbschema file that is created when you build a database project, or on one or more active databases. You can also generate the necessary scripts to make the structure of the target database match the structure of the source database. Schema Compare is only available in Visual Studio Premium and Visual Studio Ultimate.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 data in the target database match the data in the source database. Data Compare is only available in Visual Studio Premium and Visual Studio Ultimate.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 test conditions for adding reusable validation logic for your unit tests. You can run database unit tests in Visual Studio Professional, Visual Studio Premium, or Visual Studio Ultimate, but you must have Visual Studio Premium and Visual Studio Ultimate to create or modify database 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. You must have Visual Studio Premium and Visual Studio Ultimate to create or modify data generation plans.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.
Command Line Support
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.
See Also
Tasks
Walkthrough: Creating an Isolated Database Development Environment
Walkthrough: Establishing a Baseline for the Isolated Development Environment
Walkthrough: Performing Iterative Database Development in an Isolated Development Environment