Share via

An Overview of Database and Server Projects

  • Article

You can help your organization manage its database development more effectively by using Visual Studio Premium to create a database project or a server project and putting the project under version control. These offline representations contain the object definitions, settings, and deployment scripts that you could use to create a separate instance of that database (or server) or to update an existing instance.

Based on your organization's needs, you can also share parts of projects or create a composite project. For more information, see Sharing Parts of Projects later in this topic, or the "Composing Projects by Using References" section of Using References in Database Projects.

Available Project Types

By using Visual Studio 2010 Premium, you can create database projects, server projects, and Data-tier Application Component (DAC) projects.

  • Data-tier Application Component project
    A DAC is a new concept. It was introduced with SQL Server 2008 R2 and it contains the definition for a SQL Server database and the supporting instance objects that are used by a client-server or 3-tier application. A DAC includes database objects, such as tables and views, together with instance entities such as logins. You can use Visual Studio to create a DAC project, build a DAC package file, and send that DAC package file to a database administrator for deployment onto an instance of the SQL Server database engine. For a list of the features supported by DAC projects, see Features Supported in Data-tier Applications.

  • Database project
    A database project contains the definitions of all the objects for a database. In addition, it contains database settings, build settings, and deployment settings.

  • Server project
    A server project contains the definitions of the objects that reside on the server or in the master database on the server. In addition, it contains server settings, build settings, and deployment settings.

Project Structure

Solution Explorer shows your project organized by file. Each item in Solution Explorer corresponds to a saved file or a folder. In contrast, Schema View shows your project organized by object or by schema, so that you can identify objects in your database whether they are defined in separate files or not.

A database or server project can contain the following types of objects:

  • Properties Files
    Your database or server project includes files in the Properties folder that contain property values. You can control how your project is deployed by modifying those values. For example, you can specify database settings, server settings, SQLCMD variables, and database permissions. For more information, see Property Files in Database and Server Projects.

  • Data Generation Plans
    Data generation plans contain information about how you want to generate realistic and representative test data for a database that you intend to deploy or update. For more information, see Generating Test Data for Databases by Using Data Generators.

  • Schema Comparisons
    A schema comparison contains information about a specific comparison between your database project and another schema. You can re-compare your project to that schema by re-opening the .scmp file and refreshing the comparison. For more information, see Compare and Synchronize Database Schemas.

  • Schema Objects
    Schema objects are defined in a collection of .sql files that are stored in the project folder. Most objects are defined in separate files. Exceptions include columns in a table and parameters for a stored procedure or function. Columns are stored in the definition for the table, and parameters are stored in the definition for the stored procedure or function. For more information, see Creating and Modify Database and Server Objects.

  • Scripts
    Your project contains pre-deployment and post-deployment scripts, in addition to any scripts that you might use to manage your database or server. For more information, see Creating and Modify Database Scripts.

Importing Objects and Settings

After you create a project, you can import the objects and settings from a database instance or from a script. When you import a database, its object definitions are validated, and statements that cannot be parsed are put in the ScriptsIgnoredOnImport.sql file. If you import object definitions that reference objects that no longer exist, you must resolve those errors before you can build and deploy the project. For example, you might import a stored procedure that references a table that no longer exists. To resolve the error, you could remove that stored procedure.

You might need to spend a lot of time resolving such errors after you import a large schema. However, team members cannot unknowingly introduce additional errors of this type as they update the schema in Visual Studio Premium. When they modify and save any object definition, all changes are validated so that team members can fix them immediately and avoid deploying those errors to a live database.

After you resolve warnings in object definitions, you should also consider analyzing your database code for issues around design, naming, and performance. For more information, see Analyzing Database Code to Improve Code Quality.

Sharing Parts of Projects

If you want to re-use a set of files in more than one project, you can export any part of a project as a partial project. This action creates a .files file, which you can include in one or more other projects. For example, you might have some common stored procedures that you use to audit all your databases. You can define these stored procedures in one project, export them, and include them in your other projects. By taking this approach, you avoid having to maintain the same code in more than one project. For more information, see How to: Import and Export Partial Database Projects.

Security Considerations

Your database projects and any related .dbschema files contain information about your database assets. In much the same way that you would restrict access to the source code, you should restrict access to your database projects and .dbschema files.

You can use the following methods to control access to your database projects and .dbschema files:

  • Use version control for your projects and appropriate file system permissions
    You can grant access to your database projects to only those developers who require access. In addition, you can restrict permissions on any file shares that contain database project files or .dbschema files to prevent access by unauthorized individuals.

  • Partition a database project by using partial projects
    You can divide your database objects across several projects, and use version control permissions to control who can view or change those projects. For more information, see Starting Team Development of Large Databases.

  • Create a .dbschema file that has empty procedure bodies
    You can create a copy of your stored procedures that do not contain implementation, and share the .dbschema file for that copy with developers who must call those procedures but who do not have to see their implementation. For more information about how to reference .dbschema files, see Using References in Database Projects.

See Also

Tasks

Walkthrough: Create and Deploy a New Version-Controlled Database

Walkthrough: Put an Existing Database Schema Under Version Control

Concepts

Build and Deploy Databases to an Isolated Development Environment

Configuring Database Projects and Performing a Test Deployment

Writing and Changing Database Code