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 and restricting version control access to objects in the imported partial project, 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 database project that contains the definitions for a schema and two tables in your database.

  • Export objects as a partial project.

  • Create a development database project.

  • Import objects in a partial project into the development project.

  • Define stored procedures that reference imported objects.

  • Configure, build, and deploy the development project.

Prerequisites

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

Create a Source Database Project and its Objects

If you already have a database project that contains a schema and tables, you can 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, choose New, Project.

    The New Project dialog box opens.

  2. Under Installed Templates, expand the Database node, and then choose 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 choose Advanced.

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

    Note

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

  4. In Name, enter MySourceDatabase.

  5. In Location, specify the path in which you want to create the database project, and choose the OK button.

    Important

    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, choose Database Schema View.

  2. Expand the MySourceDatabase node, expand the Schemas node, open the shortcut menu for the Schemas node, and choose Add, Schema.

    The Add New Item dialog box opens.

  3. In the Templates list, choose Schema.

  4. In Name, enter Person, and then choose the Add button.

    Next, you will create tables in the new schema.

To define tables in the Person schema

  1. In Schema View, opent the shortcut menu for the Person node, and choose Add, 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, choose Table.

  3. In Name, enter Contacts, and then choose the Add button.

  4. Open the shortcut menu for the Person node, and choose Add, Table.

    The Add New Item dialog box opens.

  5. In the Templates list, choose Table.

  6. In Name, enter StateProvince, and then choose the Add button.

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

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. Open the shortcut menu for the Person node, and choose Export as Partial Project.

    The Save As dialog box opens.

  3. In File name, enter ExportedPersonTables, and choose the Save button.

    In this step, you are exporting the tables that you defined in the Person schema.

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

    The Person.schema.sql file appears.

  5. Open the shortcut menu for the Person node, and choose Export as Partial Project.

    The Save As dialog box opens.

  6. In File name, enter ExportedPersonSchema, and choose the Save button.

    In this step, you are exporting the Person schema itself.

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

  7. On the File menu, choose Close Solution. If you are prompted to save your changes to the solution, choose the Yes button.

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

To create the development database project

  1. On the File menu, choose New, Project.

    The New Project dialog box opens.

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

    Note

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

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

    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.

  4. In Name, enter MyDevelopmentDatabase.

  5. In Location, specify the path in which you want to create the database project, and choose the OK button.

    The database project is created and appears in Solution Explorer.

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

To import a partial project

  1. In Solution Explorer, open the shortcut menu for the MyDevelopmentDatabase project node, and choose Import Partial Project.

  2. In the confirmation dialog box, choose the Yes button.

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

  4. Choose the ExportedPersonSchema.files file, and then choose Open.

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

  5. Repeat steps 1 to 4, importing the ExportedPersonTables.files partial project.

  6. 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.

To define a simple stored procedure that references imported objects

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

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

  3. Open the shortcut menu for the Person node, and choose Add, Stored Procedure.

    The Add New Item dialog box opens.

  4. In Name, enter uspCountContacts, and choose the Add button.

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

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

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

  6. On the File menu, choose Save All.

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

To configure, build, and deploy the development database

  1. In Solution Explorer, open the shortcut menu for the MyDevelopmentDatabase node, and choose Properties.

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

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

  4. Under Target Database Settings, choose Edit.

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

  6. On the File menu, choose Save All.

  7. In Solution Explorer, open the shortcut menu for the MyDevelopmentDatabase node, and choose Build.

  8. In Solution Explorer, open the shortcut menu for the MyDevelopmentDatabase node, and choose 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. You can now add the projects to version control, and then set permissions to restrict which developers can modify the definitions of the stored procedures.

See Also

Concepts

Starting Team Development of Large Databases

Change History

Date

History

Reason

August 2011

Updated procedure to export (and import) the schema as a second partial project.

Customer feedback.