Starting Team Development of Databases that Reference Shared Server Objects

  • Article

Before you can manage changes to database objects and settings, you first create a database project. Then you import objects and settings from the database that you want to manage. If multiple databases reference the same server objects, you create a project for each database and a server project into which you import objects such as shared logins, keys, or custom error messages.

Note

In most team environments, only database administrators should update server objects and settings. You can structure permissions so that only administrators can create and modify the server project, and other team members can update objects in the database project.

You then put these projects under version control and deploy iterative changes to an isolated development environment. In this type of environment, team members can test their changes independently, and then share those changes with the team only after they are fully tested. By taking this approach, you help improve the quality of the database code before you deploy it to a production environment.

Specifying a Type of Project

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

Referencing the Server Project

After your projects have been created, you open the database project and declaring a reference that identifies the server project by its name. You do not need to create variables that identify a server and a database in this project, because the database and the server objects that it references always reside on the same server.

Note

You could also use a non-composite reference in this scenario if you do not want to verify server settings when you deploy. If you want to use a non-composite reference, you define a database variable, but you specify a literal whose value is "master". For more information, see Using References in Database Projects and How to: Add References to Database Projects.

Referencing System Objects

If your database project uses system objects, such as a system stored procedure, a system table, a system view, or a system catalog, you must add a reference to a "master" database. You might, for example, want to reference sys.sysobjects. If you do not add a reference to a master database that provides definitions for these objects, one or more warnings appear for each unresolved reference.

You do not have to create a server project to reference system objects. You instead add a reference to the master.dbschema file that corresponds to the database and version that you are using. The master.dbschema file can be found in [Program Files]\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\SqlServerVersion\DBSchemas, where SqlServerVersion is the version of SQL Server that you are referencing (2005 or 2008).

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks. In addition, you should determine how these tasks best fit into the development methodology of your team.

Common Tasks

Supporting Content

Learn more about database and server projects: You can read about the basic concepts of how to manage change by using database and server projects.

Get hands-on experience: You can follow the walkthrough to create and deploy a database that references shared logins.

Create database and server projects and add them to version control: You can use a wizard to create database and server projects, configure project settings, and import objects and settings from a database or server. You can also create an empty project if you want to import objects and settings later, or if you do not have permission to access the server or database from which you want to import. After you have imported your objects and settings, you can add the project to version control.

Starting Team Development of Databases

Starting Team Development of Databases that Reference Other Databases

Starting Team Development of Databases that Reference SQLCLR Objects