Sdílet prostřednictvím

Walkthrough: Partition a Database Project by Using Partial Projects

  • Článek

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 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. Under Installed Templates, expand the Database node, and then clock SQL Server.

    Poznámka

    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.

    Poznámka

    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, type MySourceDatabase.

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

    Důležité

    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.

    Poznámka

    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.

Export 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. Right-click the Person node, and click Export as Partial Project.

    The Save As dialog box opens.

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

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

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

Create 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. Under Installed Templates, expand the Database node, and then clock SQL Server.

    Poznámka

    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.

    Poznámka

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

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

Define 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 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, 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 Project

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