Walkthrough: Partition a Database Project by Using Composite Projects

  • Article

You can use composite projects to better manage a large database by partitioning it into a set of dependent database projects that are deployed together. This walkthrough illustrates the following tasks:

  • Create a database project in which your database schema is defined.

  • Create another database project in which your tables for a particular schema are defined.

  • Create a third database project in which the stored procedures for a particular schema are defined.

  • Configure, build, and deploy the third project, together with its dependencies.

Prerequisites

You must have installed Visual Studio and have access to a server that is running SQL Server.

Create a Database Project for Your Schema

To create the source database project

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

    The New Project dialog box opens.

  2. Under Installed Templates, expand the Database node, and then click SQL Server.

    Note

    If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.

  3. In the list of templates, click SQL Server 2008 Database Project.

    Note

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

  4. In Name, type MySchemaProject.

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

  6. In Solution name, type MyCompositeSolution, and click OK.

    The database project is created and appears in Solution Explorer. Next you define a schema in the project.

To define a schema

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

  2. Expand the MySchemaProject 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 click Add.

  5. On the File menu, click Save All.

    Next, you build the project.

To build the schema project

  • In Solution Explorer, right-click the MySchemaProject node, and click Build.

    Note

    You must build the project so that references to the schema that is defined in project can be resolved in the procedures that follow.

    Next, you create a project for the tables in the Person schema.

Create a Database Project for Your Tables

To create the source database project

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

    The New Project dialog box opens.

  2. Under Installed Templates, expand the Database node, and then click SQL Server.

    Note

    If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.

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

  4. In Name, type MyTablesProject.

  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 add a reference to the schema project before you can define tables in the table project.

To add a reference to the schema project

  1. In Solution Explorer, expand the MyTablesProject node, right-click the References node, and click Add Database Reference.

    The Add Database Reference dialog box opens.

  2. In Database Reference, verify that Database projects in the current solution is specified.

    Note

    If this solution did not contain the projects to which you referred, you would instead click Database project schema (.dbschema). You might use this approach if, for example, you wanted to prevent team members who were developing stored procedures from unintentionally changing the schema or table projects.

  3. Click MySchemaProject.

    Important

    Do not specify server variables and values or database variables and values when you define references in a composite project. Because no variables are defined, the referenced project is assumed to share the target server and database of the current project.

  4. Click OK.

    Next, you create two tables in the new schema.

To define tables in the Person schema

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

  2. On the Schema View toolbar, click External Elements.

    You must do this because the Person schema is defined in a different project.

  3. In Schema View, expand the MyTablesProject node, expand the Schemas node, 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.

  4. In the Templates list, click Table.

  5. In Name, type Contacts.

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

    The Add New Item dialog box opens.

  7. In the Templates list, click Table.

  8. In Name, type StateProvince.

    Next, you build the database project.

To build the tables project

  • In Solution Explorer, right-click the MyTablesProject node, and click Build.

    Next, you create a third database project for your stored procedures.

Create a Database Project for Your Stored Procedures

To create the database project for stored procedures

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

    The New Project dialog box opens.

  2. Under Installed Templates, expand the Database node, and then click SQL Server.

    Note

    If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.

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

  4. In Name, type MySprocProject.

  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 modify the stored procedures project so that it refers to the schema project.

To add a reference to the schema project

  1. In Solution Explorer, expand the MySprocProject node, right-click the References node, and click Add Database Reference.

    The Add Database Reference dialog box opens.

  2. In Database Reference, verify that Database projects in the current solution is specified.

  3. Click MySchemaProject.

    Important

    Do not specify server variables and values or database variables and values when you define references in a composite project. Because no variables are defined, all projects to which you refer are deployed to the same target server and database.

  4. Click OK.

    Next, you modify the stored procedures project so that it refers to the MyTablesProject project.

To add a reference to the tables project

  1. In Solution Explorer, right-click the References node under the MySprocProject node, and click Add Database Reference.

    The Add Database Reference dialog box opens.

  2. In Database Reference, verify that Database projects in the current solution is specified.

  3. Click MyTablesProject.

    Important

    Do not specify server variables and values or database variables and values when you define references in a composite project. Because no variables are defined, all projects to which you refer are deployed to the same target server and database.

  4. Click OK.

    Next, you create a stored procedure in the schema.

To define stored procedures that refer to the tables in the Person schema

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

  2. On the Schema View toolbar, click External Elements.

    You must do this because the Person schema is defined in a different project.

  3. In Schema View, expand the MySprocProject node, expand the Schemas node, 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 editor.

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

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

  6. On the File menu, click Save All.

    Next, you configure, build, and deploy the MySprocProject and its dependencies.

Configure, Build, and Deploy Your Database

To configure MySchemaProject for deployment

  1. In Solution Explorer, right-click the MySchemaProject 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. Click Edit next to Target Database Settings.

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

  6. In Target database name, type MySprocProject.

    Important

    You must specify the same target database name for all three projects that make up the composite project or deployment will fail.

  7. On the File menu, click Save All.

    Next, you configure properties for MyTablesProject.

To configure MyTablesProject for deployment

  1. In Solution Explorer, right-click the MyTablesProject 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. Click Edit next to Target Database Settings.

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

  6. In Target database name, type MySprocProject.

    Important

    You must specify the same target database name for all three projects that make up the composite project or deployment will fail.

  7. On the File menu, click Save All.

    Next, you configure properties for MySprocProject.

To configure MySprocProject for deployment

  1. In Solution Explorer, right-click the MySprocProject 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. Click Edit next to Target Database Settings.

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

  6. In Target database name, type MySprocProject.

    Important

    You must specify the same target database name for all three projects that make up the composite project or deployment will fail.

  7. On the File menu, click Save All.

    Next, you build the database project.

To build and deploy the development database

  1. In Solution Explorer, right-click the MySprocProject node, and click Build.

  2. In Solution Explorer, right-click the MySprocProject node, and click Deploy.

    Your database is built and deployed to the specified target server, together with its dependencies.

Next Steps

You can use this method to work on smaller sections of a database at the same time. You can also refer to the .dbschema files instead of the database projects if you want the developers who work on the stored procedures to be isolated from the table and schema definitions.

See Also

Concepts

Starting Team Development of Large Databases