Starting Team Development of Databases That Reference Other Databases

  • Article

Before you can manage changes to a database schema, you first create a database project, and then you import objects and settings from the database that you want to manage. If an object in your database references an object in another database, you must also declare the referenced server and database in the properties of your database project. Because a database project is an offline representation of the database, you can put it under version control and then deploy iterative changes to an isolated development environment. Team members can test their changes independently, and then share those changes with the team after they are fully tested. By taking this approach, you help regulate the quality of the database code before you deploy it to a production environment.

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, its object definitions are validated, and statements that cannot be parsed are added to the ScriptsIgnoredOnImport.sql file.

Declaring References to Database Projects and .dbschema files

After you import a database, you must declare the cross-database references in the properties of the project to resolve warnings. As you declare each reference, you define variables for the referenced server and database. For each variable, you can specify different values based on the different environments to which you expect to deploy the project.

When you declare a reference, you specify either another database project or a database schema file (.dbschema) as the target of the reference. Database schema files are produced when you build a database project or when you import a schema by using the command-line tool. In addition, you can specify this target either by its name or by a variable that is replaced by the appropriate value when you build the project. To simplify maintenance, you can update the objects in your database project so that they use the variables to refer to objects in other databases. For more information, see Rename References to a Server or Database. When you build the database project, the variables are replaced by the values that you specified.

You should refer to another database project as the target if the following statement is true:

  • You must change the schemas of both the database that will contain the reference and the database to which you are referring. When you deploy the database project that contains the reference, you will also deploy the database project to which you are referring.

You should refer to a database metafile if any statement in the following list is true:

  • You must refer to a database whose project is not in the current solution.

  • You must deploy a single project without deploying the projects upon which it depends.

  • You are referring to a database schema that is unlikely to change. In this scenario, you can check the database metafile in to version control.

For more detailed information, see Using References in Database Projects.

Common Tasks

You can perform the tasks in the following table with your database project. The processes in which you use these tasks might vary based on the development methodology of your team.

Common Tasks

Supporting Content

Learn more about database 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 create an isolated development environment in which you can manage changes to a database that references other databases.

Create a .dbschema file for a referenced database: By creating a .dbschema file, you can reference a database even if you have not created a database project for it. For example, you might need to reference a database that is not in development. If you import the schema for that database at a command prompt, you create a .dbschema file that you can reference in your database project.

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.

You should then create variables to specify other servers and databases to which objects in your database refer. After you create those variables, you should use them when you declare the references in the properties for your project. You can also update object definitions to use the variables instead of explicit references. By using variables, you can specify different databases based on different environments to which you deploy your database. For example, you can automatically replace the variables with values that are specific to development, test, staging, and production environments. After you declare your cross-database references, you can add the database project to version control.

Starting Team Development of Databases

Starting Team Development of Databases that Reference Shared Server Objects

Starting Team Development of Databases That Reference SQLCLR Objects

Scenario: Manage Changes to Database Server Objects

Scenario: Manage Changes to Database Server Endpoints

See Also

Concepts

Terminology Overview of Database Edition