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
On the File menu, click New and then click Project.
Under Project types, click Database Projects and then click SQL Server 2008.
Under Templates, click SQL Server 2008 Database Project.
In Name , type
Schemas
.Verify that the option Create directory for solution is selected.
In Solution Name , type
SingleTargetExample
and then click OK.Open the Schema View window.
Right-click the Schemas node, click Add, and then click Schema.
In Name, type
HumanResources
and then click Add.In Solution Explorer, right-click the Schemas project and then click Build.
Add the Solution to your source control system.
Create a Database Project to Store Table Definitions
To create table definitions
In Solution Explorer, right-click Solution 'SingleTargetExample', click Add and then click NewProject.
Under Project types, click Database Projects and then click SQL Server 2008.
Under Templates, click SQL Server 2008 Database Project.
In Name, type
Tables
.Click OK.
In Solution Explorer, right-click the References node in the Tables project and then click Add Database Reference.
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.
Click OK.
In Schema View, expand Tables and then expand Schemas.
Right-click HumanResources, click Add, and then click Table.
In Name, type
Department
and then click Add.In Solution Explorer, right-click the Tables project and then click Build.
Note
The Tables and Schemas projects should be built.
Add the Tables project to your source control system.
Create a Database Project to Store View Definitions
To create view definitions
In Solution Explorer, right-click Solution 'SingleTargetExample', click Add and then click NewProject.
Under Project types, click Database Projects and then click SQL Server 2008.
Under Templates, click SQL Server 2008 Database Project.
In Name, type
Views
.Click OK.
In Solution Explorer, right-click the References node in the Views project and then click Add Database Reference.
In the Database projects in the current solution list, select Tables.
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.
Click OK.
In Solution Explorer, right-click the References node in the Views project and then click Add Database Reference.
In the Database projects in the current solution list, select Schemas and then click OK.
In Schema View, expand Views, expand Schemas and then expand HumanResources.
Right-click Views, click Add, and then click View.
In Name, type
ViewOfDepartment
.Click Add.
ViewOfDepartment.view.sql automatically opens in the editor.
In the editor, modify the CREATE VIEW statement to read as follows:
CREATE VIEW [HumanResources].[ViewOfDepartment] AS SELECT * FROM [HumanResources].[Department]
Click Save.
In Solution Explorer, right-click the Views project and then click Build.
Note
The Views, Tables, and Schemas projects should be built.
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
In Solution Explorer, right-click the Schemas project and then click Properties.
In Project Properties, click Deploy.
In Deploy action, select Create a deployment script (.sql) and deploy the database.
Under Target Database Settings, click Edit for the Target Connection.
Under Server name, select the server that you want to use.
In Select or enter a database name, type
MyDatabase
.Click OK.
Click Save.
To set the deployment options for the Tables project
In Solution Explorer, right-click the Tables project and then click Properties.
In Project Properties, click the Deploy tab.
In Deploy action, select Create a deployment script (.sql) and deploy the database.
Under Target Database Settings, click Edit for the Target Connection.
Under Server name, select the server that you want to use.
In Select or enter a database name, type
MyDatabase
.Click OK.
Click Save.
To set the deployment options for the Views project
In Solution Explorer, right-click the Views project and then click Properties.
In Project Properties, click the Deploy tab.
In Deploy action, select Create a deployment script (.sql) and deploy the database.
Under Target Database Settings, click Edit for the Target Connection.
Under Server name, select the server that you want to use.
In Select or enter a database name, type
MyDatabase
.Click OK.
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
In Solution Explorer, select SingleTargetExample.
On the Build menu, click Build Solution.
Verify that there are no build errors.
On the Build menu, click Deploy Solution.
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
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
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
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
On the File menu, click New and then select Project.
Expand Other Project Types and then click Visual Studio Solutions.
Select Blank Solution.
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
In Solution Explorer, right-click PartialDBProj.
Click Add and then click New Project.
Expand Database Projects and then click SQL Server 2008.
Select SQL Server 2008 Database Project.
In Name, type
BaseDB
and then click OK.
To create the consuming database project
In Solution Explorer, right-click PartialDBProj.
Click Add and then click New Project.
Expand Database Projects and then click SQL Server 2008.
Select SQL Server 2008 Database Project.
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
In Solution Explorer, right-click BaseDB.
Click Add and then click Table.
Click Add.
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
In Solution Explorer, select Table1 and Table 3.
Right-click and then select Export As Partial Project.
In the Save As dialog box, click Save.
In the message box, click Yes to add the file to the current project.
The file BaseDB.files is added to the BaseDB project.
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
In Schema View, expand DerivedDB and then expand Schemas.
Expand dbo.
Right-click Views, click Add, and then click View.
In the Add New Item - <projectname> dialog box click Add.
Repeat steps 3 and 4 again.
Two new views have been added to DerivedDB: View1 and View2.
In the Editor, select View1.
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]
Click Save.
In the Editor, select View2.
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]
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
In Solution Explorer, right-click DeriveDB and select Import Partial Project.
In the message box, click Yes.
In the Open dialog box, move to the BaseDB project folder.
Select BaseDB.files and then click Open.
In Solution Explorer, expand DeriveDB.
Note
DeriveDB should now include a partial project link, BaseDBBasePath<n>.
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
In Solution Explorer, right-click the DerivedDB project and then click Properties.
In Project Properties, click the Deploy tab.
In Deploy action, select Create a deployment script (.sql) and deploy the database.
Under Target Database Settings, click Edit for the Target Connection.
Under Server name, select the server that you want to use.
In Select or enter a database name, type
MyDerivedDB
.Click OK.
Click Save.
Building and Deploying DerivedDB from the IDE
To build and deploy DerivedDB from the IDE
In Solution Explorer, select DerivedDB.
On the Build menu, click Build DerivedDB.
Verify that no build errors occurred.
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
At the Windows command prompt, type the following command:
cd %PROJECTS%\PartialDbProj\DerivedDB Msbuild DerivedDB.dbproj /t:Build
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