Starting Team Development of Large Databases

  • Article

Before you can use Visual Studio to manage changes to a database schema, you first create a database project, server project, or Data-tier Application project, and then you import objects and settings from the database that you want to manage. If you want to manage changes to a very large database, you might want to divide the objects between several database projects. By following this practice, you can control which teams or developers can add, modify, or delete code in the different sections of the database.

You can use two approaches to separate your database into smaller pieces:

  • Composite Projects — You can define sections of your database in two or more database projects (in the same solution or you can reference a compiled .dbschema file) that are linked by database project references. When you deploy the project that contains the reference, you also deploy all projects that it references. You cannot have circular references between projects in a composite project.

  • Partial Projects — You can export a section of your database project in the form of a .files file. You then create a second database project and include the partial project (the .files file). You can then set up write permissions on the original files to restrict changes to those files. Therefore, developers who work on the second project can create additional objects that refer to the read-only objects but do not modify them. When you build and deploy the second project, a full copy of the database is built, including the read-only sections. You can have circular references in a partial project.

Each approach has limitations, as detailed later in this topic.

Specifying a Type of Database Project

When you create a database project, you specify the type of project that corresponds to your version of SQL Server. For example, if the database that you want to manage is based on SQL Server 2005, you specify either SQL Server 2005 Database Project or SQL Server 2005 Wizard. If you use the wizard, you can not only create the project but also configure some build and deployment settings and import database objects and settings at the same time.

Importing Database Objects and Settings

After you create the project, you can import the objects and settings from a database instance or from a script. When you import a database from a script, its object definitions are validated, and statements that cannot be parsed are put in the ScriptsIgnoredOnImport.sql file. If you import object definitions that refer to 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 when 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 design issues, naming issues, and performance issues. For more information, see Analyzing Database Code to Improve Code Quality.

Common Tasks

Common Tasks

Supporting Content

Learn more about database projects and the limitations of partial and composite projects: You can read about the basic concepts of how to manage schema changes by using database projects.

Get hands-on practice: You can follow an introductory walkthrough to become familiar with how to partition a database project by using partial projects or by using composite projects.

Put an existing database schema under version control: You can create a project, configure project settings, and import a schema by using the database project wizard. You can also create an empty project if you want to import the schema later or if you do not have permission to access the database from which you want to import the schema. After you import the schema, you can add the project to version control.

Partition a database project to share object definitions: You can export object definitions from one database project and reuse them in another project. Even team members who can access the project into which you import the partial project definition cannot modify the imported objects. Therefore, you can control changes to subsets of your database code.

Add references to create a composite project: You create a composite project by adding references to a database project but not specifying values for the server and database variables. When you deploy a project, you also deploy any projects that it references.

Uses and Limitations of Partial Projects

The following illustration shows a typical scenario that involves partial projects:

Using Partial Projects in Database Edition

Partial Projects in Database Edition

In this example, a project contains two sets of objects. You want another developer or team to add stored procedures to the project, but you want to prevent them from making accidental changes to the other objects. To accomplish this goal by using partial projects, you must perform the following steps:

  1. You export the groups of objects, either by schema or by object type, into A.files and B.files.

  2. You create a second database project in which the other developer or team will create stored procedures (sometimes referred to as sprocs).

  3. You import the exported partial projects, A.files and B.files, into the second database project.

  4. You restrict the source code control permissions to the objects in the imported partial projects to allow only read-only access.

At this point, the other developer or team can add objects, and build and deploy their project to test their changes.

You might not be able to import the partial project (the .files file) into another database project if your database contains objects that have long names or if the path on which you created the database project is long. You can avoid these issues if you address the following suggestions:

  • Create your database projects in a folder that has a shorter path name. For example, "D:\MyProjects" might be a better choice than "C:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects".

  • Avoid very long names for database objects. Foreign keys are the most common type of object to have long names. If the name of your foreign key is "FK_ReferencingTable_ReferencedTable_ReferencedColumn1_ReferencedColumn2", errors might appear when you try to import a partial project that contains the definition of that key.

Uses and Limitations of Composite Projects

The following illustration shows a typical scenario that involves composite projects:

Using Composite Projects in Database Edition

Composite Projects in Database Edition

In this example, you can create a database project that contains the definitions of your schemas. You then create a second database project that contains the definitions of your tables and views, and a third database project that contains the definitions of any stored procedures. The third project (Database Project C) has references to the other two database projects. When you build and deploy the third project, you will also deploy the other projects automatically.

If you use composite projects, you must be able to build and deploy each project independently. You cannot have circular dependencies between the projects in a composite project. You can use composite projects to partition your database by object type. For example, you can put schemas in one project, tables and views in another project, and stored procedures in a third project.