Starting Team Development of Databases that Reference Shared Server Objects
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 create a composite project by opening 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 a composite 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 9.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. |
Related Scenarios
Starting Team Development of Databases
Starting Team Development of Databases That Reference Other Databases
Starting Team Development of Databases That Reference SQLCLR Objects
Scenario: Manage Changes to Database Server Objects
Scenario: Manage Changes to Database Server Endpoints