Collaborative Development with Team System 2008 Database Edition

By Duke Kamstra & Elizabeth Murray

Team-based database development is supported by the general distribution release (GDR) of Visual Studio Team System 2008 Database Edition in two primary ways: composite projects and partial projects. When combined with source control, these types of projects allow for multiple teams to collaborate to develop a database application.

Multi-Team Development and Code Reuse

On large database projects, multiple teams might be responsible for delivering different parts of the database functionality. If you divide the definition of your database design between several database projects, you can more easily manage changes to the database. This technique enables you to control which teams or which developers can view, add, modify, or delete code in the different sections of the database design. You can also encourage reusing parts of the database, such as object definitions that are identical in multiple databases, by defining the object in a single project.

For more information, see Starting Team Development of Large Databases.

Referencing other Database projects in Visual Studio

The GDR provides you two different ways to reference other database's projects: project references and partial references. You can use project references when you may want to use one, two, three, or four part names to reference objects defined in another project. Project references are similar to assembly references in C# or Visual Basic .NET. You can use partial references when you want to include the source code from one project into another project. Partial references are similar to using C/C++ #include files.

Database References

Database References are used to implement designs where:

  • There are multiple databases or servers (three and four part names).
  • Different parts of a single database design are implemented in separate projects (one and two part names).
  • A combination of both of the previous paramaters.

In the GDR, we refer to the scenario in the second bullet as a composite project, for example, a single database design is 'composed' of multiple database projects. A requirement of database projects that use project references is that each project must be able to be deployed independently. It is okay if there is a deployment order requirement.

Different teams or team members may have different access permissions to the projects in the source control system.

Partial Projects

Partial projects are used to share source code files between different database projects. Partial projects consist of a producing project that contains the source code to be shared and a consuming project which imports links to the source files being consumed.

The team or team members who are working on the source code in the producing project will generally have read/write permission to the project in the source control system. Conversely, the team or team members who are working on the project that consumes the source files only have read-only permission.

Example: Using Composite Projects to Create a Single-Target Database Application

In this example, a company is developing an in-house database application. Three different teams are contributing to the application, each working on different aspects. Each team works in a single project that is contained in a shared solution.

The database design includes three database projects: Schemas, Tables, and Views. These projects are contained in the solution SingleTargetExample.

Create a Database Project to Store Schema Definitions

To create the schema database project

  1. On the File menu, click New and then click Project.

  2. Under Project types, click Database Projects and then click SQL Server 2008.

  3. Under Templates, click SQL Server 2008 Database Project.

  4. In Name , type Schemas.

  5. Verify that the option Create directory for solution is selected.

  6. In Solution Name , type SingleTargetExample and then click OK.

  7. Open the Schema View window.

  8. Right-click the Schemas node, click Add, and then click Schema.

  9. In Name, type HumanResources and then click Add.

  10. In Solution Explorer, right-click the Schemas project and then click Build.

  11. Add the Solution to your source control system.

Create a Database Project to Store Table Definitions

To create table definitions

  1. In Solution Explorer, right-click Solution 'SingleTargetExample', click Add and then click NewProject.

  2. Under Project types, click Database Projects and then click SQL Server 2008.

  3. Under Templates, click SQL Server 2008 Database Project.

  4. In Name, type Tables.

  5. Click OK.

  6. In Solution Explorer, right-click the References node in the Tables project and then click Add Database Reference.

  7. In the Database projects in the current solution list, verify that Schemas is selected.

    This step adds a project reference from the Schemas project to the Tables project.

  8. Click OK.

  9. In Schema View, expand Tables and then expand Schemas.

  10. Right-click HumanResources, click Add, and then click Table.

  11. In Name, type Department and then click Add.

  12. In Solution Explorer, right-click the Tables project and then click Build.

    Note

    The Tables and Schemas projects should be built.

  13. Add the Tables project to your source control system.

Create a Database Project to Store View Definitions

To create view definitions

  1. In Solution Explorer, right-click Solution 'SingleTargetExample', click Add and then click NewProject.

  2. Under Project types, click Database Projects and then click SQL Server 2008.

  3. Under Templates, click SQL Server 2008 Database Project.

  4. In Name, type Views.

  5. Click OK.

  6. In Solution Explorer, right-click the References node in the Views project and then click Add Database Reference.

  7. In the Database projects in the current solution list, select Tables.

  8. Select Suppress errors caused by unresolved references in the referenced project. This option prevents errors that might be caused because the declaration of the HumanResources schema is not in the Tables project.

  9. Click OK.

  10. In Solution Explorer, right-click the References node in the Views project and then click Add Database Reference.

  11. In the Database projects in the current solution list, select Schemas and then click OK.

  12. In Schema View, expand Views, expand Schemas and then expand HumanResources.

  13. Right-click Views, click Add, and then click View.

  14. In Name, type ViewOfDepartment.

  15. Click Add.

    ViewOfDepartment.view.sql automatically opens in the editor.

  16. In the editor, modify the CREATE VIEW statement to read as follows:

    CREATE VIEW [HumanResources].[ViewOfDepartment]
    AS SELECT * FROM [HumanResources].[Department]
    
  17. Click Save.

  18. In Solution Explorer, right-click the Views project and then click Build.

    Note

    The Views, Tables, and Schemas projects should be built.

  19. Add the Views project to your source control system.

Building and Deploying

Using the deployment tools in the IDE is the easiest way to deploy a database design because the IDE automatically handles the build order for each project. You can also decide to build and deploy from the command line with Team Build.

Setting Deployment Options for Each Project

In this example, the composite project is to be deployed to a new SQL Server database that is named MyDatabase. Each project in the solution must be configured to deploy to the same database.

To set deployment options for the Schemas project

  1. In Solution Explorer, right-click the Schemas project and then click Properties.

  2. In Project Properties, click Deploy.

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

  4. Under Target Database Settings, click Edit for the Target Connection.

  5. Under Server name, select the server that you want to use.

  6. In Select or enter a database name, type MyDatabase.

  7. Click OK.

  8. Click Save.

To set the deployment options for the Tables project

  1. In Solution Explorer, right-click the Tables project and then click Properties.

  2. In Project Properties, click the Deploy tab.

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

  4. Under Target Database Settings, click Edit for the Target Connection.

  5. Under Server name, select the server that you want to use.

  6. In Select or enter a database name, type MyDatabase.

  7. Click OK.

  8. Click Save.

To set the deployment options for the Views project

  1. In Solution Explorer, right-click the Views project and then click Properties.

  2. In Project Properties, click the Deploy tab.

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

  4. Under Target Database Settings, click Edit for the Target Connection.

  5. Under Server name, select the server that you want to use.

  6. In Select or enter a database name, type MyDatabase.

  7. Click OK.

  8. Click Save.

Deploying SingleTargetExample from the IDE

Now you are ready to deploy SingleTargetExample. The deployment process will create the database MyDatabase on the SQL Server that you specified.

To build and deploy SingleTargetExample from the IDE

  1. In Solution Explorer, select SingleTargetExample.

  2. On the Build menu, click Build Solution.

  3. Verify that there are no build errors.

  4. On the Build menu, click Deploy Solution.

  5. Verify that MyDatabase was created on the server that you specified.

Building and Deploying SingleTargetExample from the Command Line

You can also build and deploy SingleTargetExample from the command line using MSBuild tasks. This is useful when you want to use a build system such as Team Foundation Build. Unlike building and deploying from the IDE, you must manually specify the correct build and deployment order for the projects in the solution.

Note

See the Appendix for documentation on the MSBuild tasks and their optional parameters.

To build and Deploy SingleTargetExample using MSBuild

  1. At the Windows command prompt, type the following commands to build and deploy the Schemas project:

    cd %PROJECTS%\SingleTargetExample\Schemas
    msbuild Schemas.dbproj /t:Build
    msbuild Schemas.dbproj /t:Deploy
    
  2. Type the following command to build and deploy the Tables project:

    cd %PROJECTS%\SingleTargetExample\Tables
    msbuild Tables.dbproj /t:Build
    msbuild Tables.dbproj /t:Deploy
    
  3. Type the following command to build and deploy the Views project:

    cd %PROJECTS%\SingleTargetExample\Views
    msbuild Views.dbproj /t: Build
    msbuild Views.dbproj /t:Deploy
    

Example: Using Partial Projects to Create a Database Design

In this example, Team A is developing an in-house database that contains three tables. Team B has been tasked with creating views of the tables created by Team A. The following procedures show how Team A and Team B use partial projects to accomplish these tasks.

Note

This example is based on the following blog entry by Data Dude: Partial Projects.

Creating the Solution and Project Structure

Partial database projects include one or more producing projects and one consuming project in a single solution. Let's create the solution and project structure Teams A and B would use to collaborate.

To create the PartialDBProj solution

  1. On the File menu, click New and then select Project.

  2. Expand Other Project Types and then click Visual Studio Solutions.

  3. Select Blank Solution.

  4. In Name, type PartialDBProj and then click OK.

After you have the solution, you can then add the producing and consuming database projects.

To create the producing database project

  1. In Solution Explorer, right-click PartialDBProj.

  2. Click Add and then click New Project.

  3. Expand Database Projects and then click SQL Server 2008.

  4. Select SQL Server 2008 Database Project.

  5. In Name, type BaseDB and then click OK.

To create the consuming database project

  1. In Solution Explorer, right-click PartialDBProj.

  2. Click Add and then click New Project.

  3. Expand Database Projects and then click SQL Server 2008.

  4. Select SQL Server 2008 Database Project.

  5. In Name, type DerivedDB and then click OK.

Adding Tables to the Base Project

You can now add three new tables to BaseDB.

To add tables to BaseDB

  1. In Solution Explorer, right-click BaseDB.

  2. Click Add and then click Table.

  3. Click Add.

  4. Repeat steps 1 through 3, two more times.

    The BaseDB project should have three new tables named Table1, Table2, and Table3.

You can now create Views of the data in BaseDB.

Exporting Source Files from the Producing Project to the Consuming Project

Your first step to create a database project that imports source code from another project is to export the required source files from the producing project. In this case, Team A would export two of the three tables in the producing project, at the request of Team B.

To export source code to DerivedDB

  1. In Solution Explorer, select Table1 and Table 3.

  2. Right-click and then select Export As Partial Project.

  3. In the Save As dialog box, click Save.

  4. In the message box, click Yes to add the file to the current project.

    The file BaseDB.files is added to the BaseDB project.

  5. Add the solution to your source control system.

    Note

    BaseDB.files file is checked into source control because it is part of the BaseDB project.

Adding Views to the Consuming Project

In this step, we'll add views that reference two tables from BaseDB: Table1 and Table3.

To add 2 views to DerivedDB

  1. In Schema View, expand DerivedDB and then expand Schemas.

  2. Expand dbo.

  3. Right-click Views, click Add, and then click View.

  4. In the Add New Item - <projectname> dialog box click Add.

  5. Repeat steps 3 and 4 again.

    Two new views have been added to DerivedDB: View1 and View2.

  6. In the Editor, select View1.

  7. Change the SQL in the file to read:

    CREATE VIEW [dbo].[View1]
    AS SELECT [T1].[column_1], [T1].[column_2] 
    FROM [dbo].[Table1] AS [T1]
    
  8. Click Save.

  9. In the Editor, select View2.

  10. Change the SQL in the file to read:

    CREATE VIEW [dbo].[View2]
    AS SELECT [T3].[column_1], [T3].[column_2] 
    FROM [dbo].[Table3] AS [T3]
    
  11. Click Save.

Importing the Partial Project

For the views to work, we must access Table1 and Table3 from DerivedDB. In this step, we'll import a read-only copy of the two tables in BaseDB.

To import Table1 and Table 3 into the DerivedDB project

  1. In Solution Explorer, right-click DeriveDB and select Import Partial Project.

  2. In the message box, click Yes.

  3. In the Open dialog box, move to the BaseDB project folder.

  4. Select BaseDB.files and then click Open.

  5. In Solution Explorer, expand DeriveDB.

    Note

    DeriveDB should now include a partial project link, BaseDBBasePath<n>.

  6. In Schema View, expand DerivedDB, expand Schemas, expand dbo and then expand Tables.

    Note

    Table1 and Table3 from the project BaseDB should now appear.

Deploying DerivedDB

We are now ready to build and deploy the partial database project. You can decide to build and deploy DerivedDB using the IDE or the command line.

Setting Deployment Options

In this example, the derived project is to be deployed to a new SQL Server database that is named MyDerivedDB.

To set deployment options for the DerivedDB project

  1. In Solution Explorer, right-click the DerivedDB project and then click Properties.

  2. In Project Properties, click the Deploy tab.

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

  4. Under Target Database Settings, click Edit for the Target Connection.

  5. Under Server name, select the server that you want to use.

  6. In Select or enter a database name, type MyDerivedDB.

  7. Click OK.

  8. Click Save.

Building and Deploying DerivedDB from the IDE

To build and deploy DerivedDB from the IDE

  1. In Solution Explorer, select DerivedDB.

  2. On the Build menu, click Build DerivedDB.

  3. Verify that no build errors occurred.

  4. On the Build menu, click Deploy DerivedDB.

Building and deploying DerivedDB from the command line

You can also use MSBuild to build and deploy DerivedDB.

To build and deploy DerivedDB from the command line

  1. At the Windows command prompt, type the following command:

    cd %PROJECTS%\PartialDbProj\DerivedDB
    Msbuild DerivedDB.dbproj /t:Build
    
  2. Once the build has completed, type the following to deploy:

    Msbuild DerivedDB.dbproj /t:Deploy
    

Appendix--MSBuild Tasks

Syntax:

Msbuild <ProjectFile>.dbproj /t:[Build | Deploy | RunCodeAnalysis] <options>

Note

This syntax applies to Visual Studio Team System 2008 Database Edition GDR only.

Build Task Options

Option Default Value Description

DatabaseSchemaProviderName

Set on the Project Settings tab in Project Properties.

The assembly which implements the Database Schema Provider used by the project.

Source

 

Collection of folders where the .sql files for the project are stored.

ReferencePath

 

The fully qualified path of the project.

CatalogPropertiesFile

Properties\Database.sqlsettings

The path, relative to ReferencePath, and the file name of the .sqlsettings file.

ServerPropertiesFile

Properties\Server.sqlsettings

The path, relative to ReferencePath, and the file name of the .sqlsettings file. This parameter is only relevant for SQL Server projects.

OutputDBSchema

<projectname>.dbschema

The name of the output .dbschema file.

TreatWarningsAsErrors

False

Determines whether warnings are treated as errors.

SuppressWarnings

<null>

A comma delimited list of the warnings that should be suppressed during build.

AllowServerObjects

False for Database projects

-OR-True for Server projects

Controls whether server objects, such as logins, are enabled in the project.

AllowDatabaseObjects

True

Controls whether database objects, such as tables, are enabled in the project.

ModelCollation

Set on the Project Settings tab in project properties.

The collation of settings that are used for the internal model representation of the schema.

Deploy Task Options

Option Default Value Description

SourceModel

 

The fully qualified path of the output .dbschema file.

DeploymentManifest

<projectname>.deploymentmanifest

Fully qualified path and file name of the deployment manifest file.

ConnectionString

Set on the Deploy tab of the project's properties.

The SQL Server connection string to the database that will be created or modified.

DatabaseName

Set on the Deploy tab of the project's properties.

The name of the database on the SQL server.

DeployToScript

True

Determines whether to generate a .sql script representing the changes that will be made to the target database.

DeployScriptFile

The project folder.

Destination to write deployment script to.

DeployToDatabase

False

Determines whether the changes are made to the target database.

DeploymentConfigurationFile

Properties\Database.sqldeployment

-OR-Properties\Master.sqldeployment

The path and the file name relative to the project of the .sqldeployment file.

SqlCommandVariablesFile

Properties\Database.sqlcmdvars

-OR-Properties\Master.sqlcmdvars

The path and the file name relative to the project of the .sqlcmdvars file.

RunCodeAnalysis Options

Option Default Value Description

DatabaseSchemaProviderName

Set on the Project Settings tab in the project's properties.

The assembly which implements the Database Schema Provider used by the project.

Source

 

Collection of folders where the .sql files for the project are stored.

CatalogPropertiesFile

Properties\Database.sqlsettings

The path, relative to ReferencePath, and the file name of the .sqlsettings file.

ServerPropertiesFile

Properties\Server.sqlsettings

The path, relative to ReferencePath, and the file name of the .sqlsettings file. This parameter is only relevant for SQL Server projects.

TreatWarningsAsErrors

False

Determines whether warnings are treated as errors.

SuppressWarnings

<null>

A comma delimited list of the warnings that should be suppressed during build.

CodeAnalysisRules

<null>

List of rules that have been disabled or configured to treat warnings as errors.

ResultsFile

<projectpath>\StaticCodeAnalysis.Results.xml

The fully qualified path and file name of the output file created.

GenerateAnalysisLog

True

Determines whether to generate a log during the analysis process.

ProjectFolder

<projectpath>

The fully qualified path of the rules suppression file.

AllowServerObjects

False for Database projects

-OR-True for Server projects

Controls whether server objects, such as logins, are enabled in the project.

AllowDatabaseObjects

True

Controls whether database objects, such as Tables, are enabled in the project.

ModelCollation

Set in Project Settings tab of the project's properties.

The collation settings that are used for the internal model representation of the schema.

See Also

Other Resources

Starting Team Development of Databases That Reference Other Databases
Starting Team Development of Databases that Reference Shared Server Objects