Walkthrough: Partition a Database Project by Using Partial Projects

  • Article

You can use partial projects to isolate development to certain objects in a database project. By splitting a database into partial projects, you can prevent users from accidentally changing a part of the database project that they should not be updating.

You can export any object or group of objects as a partial project. For example, you might export a schema definition and its contents. You might also export objects by object type, putting tables in one project, views in another, and so on.

In this walkthrough, you will follow these steps:

  • Create a source project that contains the definitions for a schema and two tables in your database

  • Export a partial project that contains the definition for a schema and the table in that schema

  • Create a development project

  • Import the partial project into the development project

  • Define stored procedures to the development project that reference the table that is defined in the partial project

  • Configure, build, and deploy the development project

Prerequisites

You must have installed Visual Studio Team System Database Edition and have write access to a server that is running SQL Server.

Creating a Source Database Project and its Objects

If you already have a database project that contains a schema and tables, you could use it instead of the project that you create in this step of the walkthrough.

To create the source database project

  1. On the File menu, point to New, and click Project.

    The New Project dialog box opens.

  2. In the Project types list, expand the Database Projects node, and then click SQL Server 2005.

    Note

    If your target deployment database has a different version of SQL Server, you can specify the type of project that matches your version of SQL Server.

  3. In the Templates list, click SQL Server 2005 Database Project.

  4. In Name, type MySourceDatabase.

  5. In Location, type or browse to the path in which you want to create the database project, and click OK.

    Important noteImportant Note:

    If the path is too long, you might receive errors when you try to import the partial project later in this walkthrough. You can use shorter paths to avoid these errors.

    The database project is created and appears in Solution Explorer.

    Next, you will define a schema and tables in that schema.

To define a schema

  1. On the View menu, click Database Schema View.

  2. Expand the MySourceDatabase node, expand the Schemas node, right-click the Schemas node, point to Add, and click Schema.

    The Add New Item dialog box opens.

  3. In the Templates list, click Schema.

  4. In Name, type Person, and then click Add.

    Next, you will create tables in the new schema.

To define tables in the Person schema

  1. In Schema View, right-click the Person node, point to Add, and click Table.

    Note

    To keep this walkthrough brief, you do not update the table definitions.

    The Add New Item dialog box opens.

  2. In the Templates list, click Table.

  3. In Name, type Contacts, and then click Add.

  4. Right-click the Person node, point to Add, and click Table.

    The Add New Item dialog box opens.

  5. In the Templates list, click Table.

  6. In Name, type StateProvince, and then click Add.

    Next, you will export the Person schema and its objects.

Exporting Objects as a Partial Project

To export objects as a partial project

  1. In Solution Explorer, expand the Schemas Objects node, and expand the Schemas node.

    The Person node appears.

  2. Expand the Database Level Objects node, expand the Security node, and expand the Schemas node.

    The Person.schema.sql file appears.

  3. Click the Person node. While holding down the CTRL key, click the Person.schema.sql file.

    Both the Person node and the Person.schema.sql file are highlighted.

  4. Right-click the Person node, and click Export as Partial Project.

    The Save As dialog box opens.

  5. In File name, type ExportedPersonSchema, and click Save.

    Make note of the folder in which you saved the partial project.

  6. On the File menu, click Close Solution. If you are prompted to save your changes to the solution, click Yes.

    Next, you will create a project in which to develop stored procedures that refer to the tables in the Person schema.

Creating a Development Database Project

To create the development database project

  1. On the File menu, point to New, and click Project.

    The New Project dialog box opens.

  2. In the Project types list, expand the Database Projects node, and then click SQL Server 2005.

    Note

    If your target deployment database has a different version of SQL Server, you can specify the type of project that matches your version of SQL Server.

  3. In the Templates list, click SQL Server 2005 Database Project.

  4. In Name, type MyDevelopmentDatabase.

  5. In Location, type or browse to the path in which you want to create the database project, and click OK.

    The database project is created and appears in Solution Explorer.

    Next, you will import the partial project that you exported in a previous procedure.

Importing Objects in a Partial Project

To import a partial project

  1. In Solution Explorer, right-click the MyDevelopmentDatabase project node, and click Import Partial Project.

  2. In the confirmation dialog box, click Yes.

  3. In the Open dialog box, browse to the folder that contains the partial project that you exported from MySourceDatabase.

  4. Click the ExportedPersonSchema.files file, and then click Open.

    The project is unloaded and reloaded, and a folder named MySourceDatabaseBasePath appears in Solution Explorer.

  5. Expand the MySourceDatabaseBasePath folder.

    This folder contains the files that you exported from the source database.

    Next, you will define a stored procedure that refers to a table that you defined in the Person schema.

Defining Stored Procedures that Reference Imported Objects

To define a simple stored procedure

  1. On the View menu, click Database Schema View.

  2. In Schema View, expand the MyDevelopmentDatabase node, and expand the Schemas node.

  3. Right-click the Person node, point to Add, and click Stored Procedure.

    The Add New Item dialog box opens.

  4. In Name, type uspCountContacts, and click Add.

    The stored procedure is added to the project and appears in the Transact-SQL (T-SQL) editor.

  5. In the T-SQL editor, update the code to match the following:

    CREATE PROCEDURE [Person].[uspCountContacts]
AS
SELECT COUNT(*) FROM [Person].[Contacts]

  6. On the File menu, click Save All.

    Next, you will configure the development database project, build it, and then deploy it to your target database.

Configure, Build, and Deploy the Development Database

To configure, build, and deploy the development database

  1. In Solution Explorer, right-click the MyDevelopmentDatabase node, and click Properties.

  2. On the properties page, click the Deploy tab.

  3. In the Deploy action list, click Create a deployment script (sql) and deploy to database.

  4. Under Target Database Settings, click Edit.

  5. Specify a connection to the target server where you want to deploy this project, and then click OK.

  6. On the File menu, click Save All.

  7. In Solution Explorer, right-click the MyDevelopmentDatabase node, and click Build.

  8. In Solution Explorer, right-click the MyDevelopmentDatabase node, and click Deploy.

    Your database is deployed to the server that you specified.

Next Steps

The deployment includes the Person schema, the tables that you defined in the schema, and the stored procedure. Developers who work on the MyDevelopmentDatabase project can modify the definitions of only the stored procedures, not the tables.

See Also

Concepts

Terminology Overview of Database Edition

Starting Team Development of Large Databases