Walkthrough: Creating an Isolated Database Development Environment

Switch View

Walkthrough: Creating an Isolated D...

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2005/.NET Framework 2.0

Other versions are also available for the following:

Walkthrough: Creating an Isolated Database Development Environment

In this walkthrough, you set up an isolated development environment so that each database developer can make and test changes without affecting other members of the team. The isolated database development environment is based on the database project that contains the definitions for all of the objects that are in the production database. You create the project, import the database schema from a production database, and configure database project settings. You create a data generation plan and then put the database project and all associated files under version control to share with your team.

You are playing an administrative role in this walkthrough, as you define the environment that each developer on the team will use to do his or her development work. Before each developer finishes a work item, he or she uses Data Generator to put realistic but fake data into the updated schema and then runs database unit tests to verify that the schema changes work as expected. When work items are completed, each developer will check his or her changes into version control where the team can pick them up. By running tests before changes are checked in, each member of the team minimizes risk to the overall effort.

Tasks illustrated in this walkthrough include:

How to create a database project by using the New Database Project Wizard.
How to run the New Database Project Wizard to import the database schema, specify database project properties, and specify build and deployment properties.
How to examine the results of the import operation.
How to create a data generation plan and customize it to generate more realistic test data.
How to add the project to version control.

Prerequisites

You must have installed Microsoft Visual Studio Team Edition for Database Professionals and have access to the AdventureWorks sample database for Microsoft SQL Server 2005. To add your project to version control, you must have installed version control software, such as Microsoft Visual Studio 2005 Team Foundation Server or Visual SourceSafe.

For information about how to install the AdventureWorks sample database, see Installing AdventureWorks Sample Databases and Samples.

To create a database project

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

The New Project dialog box appears.
In Project types, expand the Database Projects node, and click Microsoft SQL Server.
In the Templates list, click SQL Server 2005 Wizard.
In Name, type AdvWorksSandbox.
Accept the default Location and Solution Name.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

The New Database Project Wizard appears. Next you use the wizard to configure your database project and import the initial database schema.

To configure your project and import a database schema

Click Next after you read the Welcome page.
Because the AdventureWorks database contains objects in multiple schemas, select the Organize my project by Schema check box.

Important

You cannot change a project's organization after you create it.
In Specify the default schema to use for objects you create in your database project, type HumanResources.
On the Set Database Options page, accept the default settings, and click Next.

Note

You can change these options after the project has been created. For more information, see How to: Configure Database Projects for Build and Deployment.
On the Import Database Schema page, select the Import existing schema check box.
In Source Database connection, click the connection that corresponds to the server and database that contains your AdventureWorks database. If the connection does not already exist, click New Connection to create it. If you do not specify a connection, the database project will be created, but no schema will be imported.

Note

You can import a database schema later, if the database project does not already contain database objects. For more information, see How to: Import a Database Schema.
Accept the default import options, and click Next.
On the Configure Build/Deploy page, in Target connection, click the Browse button, and specify a connection to the database server where you want to create your isolated development environment.
In Target database name, type AdvWorksSandbox if it is not already specified.
In Default deployment collation, click Use the collation of the database project.

This step makes the target database use the database project collation.
Clear the Block incremental deployment if data loss might occur check box.

Because the isolated development environment will contain only generated test data, you can allow data loss to occur.
Click Finish to create the database project and import the database.

Important
You cannot change a project's organization after you create it.

Note
You can change these options after the project has been created. For more information, see How to: Configure Database Projects for Build and Deployment.

Note
You can import a database schema later, if the database project does not already contain database objects. For more information, see How to: Import a Database Schema.

To examine the resulting project

In Solution Explorer, expand the AdvWorksSandbox node, and expand the Schema Objects child node.
Explore the sub-nodes that are underneath the Schema Objects node in the hierarchy.

Solution Explorer contains the files that define the objects that are in the schema of the database from which you imported them.
In Schema View, expand the AdvWorksSandbox node.
Explore the sub-nodes that are underneath the AdvWorksSandbox node in the hierarchy.

Schema View contains the objects that are defined in the files that appear in Solution Explorer.
In Solution Explorer, click the AdvWorksSandbox node.
On the Project menu, click AdvWorksSandbox Properties.
On the Properties page, click the Build tab.
In Target database name, type AdvWorksSandbox if it is not already specified.
On the File menu, click Save Selected Items.

Each developer will specify the connection string to the database server where they want to host his or her copy of the database.

To deploy the project to your isolated development environment

In Solution Explorer, click the AdvWorksSandbox node.
On the Build menu, click Deploy ProjectName.

A build script is generated for your database project and then deployed to the database server where you chose to host your isolated development environment. When deployment is completed, Deployment Succeeded appears in the Output window.

Next you specify a data generation plan to fill the isolated development database with realistic test data.

Generate Test Data

Now that you have an isolated database against which you can develop, you need test data that can drive your unit tests. First, you create a default data generation plan, and then you customize that plan to produce more realistic data. In this example, you only configure data generation for a subset of the tables and columns in the database. You will configure data generation for the Employee table and the StateProvince table.

The original data in the StateProvince table is not sensitive, so you can use the original data to populate the isolated development database.

However, you should make the following changes to the default generation plan for the Employee table:

Title should be one of the following: Developer, Sr. Developer, Tester, Sr. Tester, Project Manager, Sales Associate, or Sr. Sales Associate.
Birthdate must be at least 18 years ago and no earlier than January 1, 1930. This specification matches the constraint on the column in the database.
Marital Status must be 'M' or 'S'.
Gender must be 'M' or 'F'.
HireDate must be on or before today's date and on or after January 1, 2000.
VacationHours should have a Normal distribution over the range -10 to 160.
ModifiedDate must be on or before today's date and on or after January 1, 2000. Most of the records will have been updated recently.
AdditionalContactInfo in the Contact table must come from the source AdventureWorks database.

To create a data generation plan

In Solution Explorer, expand the AdvWorksSandbox node, and click the Data Generation Plans folder.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.
In the Templates list, click Data Generation Plan.
In Name, type AWGenPlan.dgen, and then click Add.

A data generation plan named AWGenPlan is added to the database project and opened in the editor. The database schema is imported into the data generation plan. Next you modify that plan to generate more realistic test data.

To restrict data generation to the tables of interest

Right-click in the data generation plan, and click Exclude All Tables From Data Generation.
In the data generation plan, select the check boxes for the following tables: HumanResources.Employee, Person.Contact, Person.CountryRegion, and Person.StateProvince.

Next you will define the data generation plan for the StateProvince table.

To define the data generation plan for the StateProvince table

Click [Person].[StateProvince] in the data generation plan.
On the Data menu, point to Data Generator, and click Column Details.

The Column Details window appears and displays the columns of the [Person].[StateProvince] table, their data types, the default generator that is associated with each column, and the generator result.
In the Column Details pane, click StateProvinceCode, and set the Generator field to Data Bound Generator.

This step indicates that you want to populate this column with the results of a query.
On the View menu, click Properties Window.
In the Properties window, in the Generator section, in the Connection Information property, click the connection that corresponds to the database from which you imported the database schema in a previous procedure.

This step specifies the database connection that you want to use to run the query that will generate data for this column.
In the Properties window, in the Generator section, in the Select Query property, set the query string to SELECT [StateProvinceCode] FROM [Person].[StateProvince].
In the Column Details pane, in the Generator Output field for the StateProvinceCode, click [OutputTable1].[StateProvinceCode].

This step selects the column in the result set that you want to associate with this column.
In the Column Details pane, click Modified Date.
In the Properties window, in the Generator section, set the Max property to today's date because records could not have been modified in the future.
In the Properties window, in the Generator section, set the Min property to 1/1/2000 12:00:00 AM. For this example, assume that the company started collecting data at the start of the year 2000.

Next you will specify the data generation plan for the Contact table.

To define the data generation plan for the Contact table

Click [Person].[Contact] in the data generation plan.
On the Data menu, point to Data Generator, and click Column Details.

The Column Details window appears and displays the columns of the [Person].[Contact] table, their data types, the default generator that is associated with each column, and the generator result.
In the Column Details pane, click AdditionalContactInfo, and set the Generator field to Data Bound Generator.

This step indicates that you want to fill this column with the results of a query.
On the View menu, click Properties Window.
In the Properties window, in the Generator section, in the Connection Information property, click the connection that corresponds to the database from which you imported the database schema in a previous procedure.

This step specifies the database connection that you want to use to run the query that will generate data for this column.
In the Properties window, in the Generator section, in the Query Select Query property, set the query string to SELECT * FROM [Person].[Contact].
In the Column Details pane, in the Generator Output field for the StateProvinceCode, click [OutputTable1].[AdditionalContactInfo].

This step selects the column in the result set that you want to associate with this column.
In the Column Details pane, click Modified Date.
In the Properties window, in the Generator section, set the Max property to today's date because records could not have been modified in the future.
In the Properties window, in the Generator section, set the Min property to 1/1/2000 12:00:00 AM. For this example, assume that the company started collecting data at the start of the year 2000.

Next you will specify the data generation plan for the Employee table.

To define the data generation plan for the Employee table

Click [HumanResources].[Employee] in the data generation plan.
In the Column Details pane, click Title, and set the Generator field to RegularExpression.
In the Properties window, in the Generator section, set the Expression property to (Developer|Sr\. Developer|Tester|Sr\. Tester|Project Manager|Sales Associate|Sr\. Sales Associate).
In the Column Details pane, click BirthDate. By default, the Generator field is set to DateTime.
In the Properties window, in the Generator section, set the Max property to 18 years before today's date (for example, 10/30/1987 11:59:59 PM). Also set the Min property to 1/1/1930 12:00:00 AM.
In the Column Details pane, click MaritalStatus, and set the Generator field to RegularExpression.
In the Properties window, in the Generator section, set the Expression property to (M|S).
In the Column Details pane, click Gender, and set the Generator field to RegularExpression.
In the Properties window, in the Generator section, set the Expression property to (M|F).
In the Column Details pane, click HireDate. By default, the Generator field is set to DateTime.
In the Properties window, in the Generator section, set the Max property to today's date (for example, 10/30/2006 11:59:59 PM). Also set the Min property to 1/1/2000 12:00:00 AM.
In the Column Details pane, click VacationHours. By default, the Generator field is set to SmallInt.
In the Properties window, in the Generator section, set the Distribution property to Normal.
In the Properties window, in the Generator section, set the Max property to 160 and the Min property to -10.
In the Column Details pane, click ModifiedDate. By default, the Generator field is set to DateTime.
In the Properties window, in the Generator section, set the Distribution property to ExponentialInverse. Also set the Max property to today's date (for example, 10/30/2006 11:59:59 PM) and the Min property to 1/1/2000 12:00:00 AM.

Next you adjust the number of rows being generated for these tables.

To adjust the number of rows being generated

Click [Person].[CountryRegion] in the data generation plan.
In the Rows to Insert field, type 10.
Click [HumanResources].[Employee] in the data generation plan.
In the Rows to Insert field, type 25.
Click [Person].[StateProvince] in the data generation plan.
In the Related Table field, click [Person].[CountryRegion].
In the Ratio to Related Table field, type 2:1.

This step specifies that you want to generate twice as many rows for the [Person].[StateProvince] table as for the [Person].[CountryRegion] table.
On the File menu, click Save AWGenPlan.dgen to save your changes to the data generation plan.

To preview the data generation and generate test data

The HumanResources.Employee table contains triggers that might interfere with data generation if you run data generation more than once. To perform data generation as part of an automated test run, you must disable (and later enable) those triggers as part of your unit tests.

To run data generation multiple times as part of this procedure, you must follow the procedure To disable and enable the triggers in the Employee table around data generation. To run data generation only once, follow the procedure To verify the data generation plan results.

To preview the data generation and generate test data

On the Data menu, point to Data Generator, and click Preview Data Generation.

The Data Generation Preview window appears and shows a sample of the data that will be generated. If the data meets the criteria that you specified, continue to the next step. If you have to adjust the data generation plan, make the appropriate changes, and then repeat this step.
On the Data menu, point to Data Generator, and click Generate Data.

The Generate Data for Target Database dialog box appears.
In Target Database, click the connection to the AdvWorksSandbox database, and click OK.
When you are asked "Do you want to clear the contents of the selected tables before inserting new rows?" click Yes.

Data is generated according to your specifications. When generation has completed, a green check mark appears in each row to indicate that all data was generated successfully. If data was not generated, errors appear in the Error List window.
Skip to the procedure To verify the data generation plan results.

To disable and enable triggers in the Employee table around data generation

Before you generate data, open the Data menu, point to T-SQL Editor, and click New Query Connection.
If a connection exists for the database in your isolated development environment, go to step 7.
Click New Connection.

The Connection Properties dialog box appears.
In Server name, type or click the name of the database server to which you deployed the database, such as (local)\SQLExpress.
In Log on to the Server, click Use Windows Authentication.
In Connect to a database, click Select or enter a database name, type AdvWorksSandbox, and click OK.

The connection is created and added to the list in the Connect to Database dialog box.
In the Connect to Database dialog box, click the connection string that corresponds to the database in your isolated development environment, and click OK.

The Transact-SQL (T-SQL) editor appears with an active connection to the AdvWorksSandbox database.
Click OK.

The Transact-SQL (T-SQL) editor appears.

In the T-SQL editor, type the following T-SQL:

DISABLE TRIGGER dEmployee on [HumanResources].[Employee];
DISABLE TRIGGER uEmployee on [HumanResources].[Employee];
GO

On the T-SQL editor toolbar, click Execute SQL.

On the Messages tab, the following appears:

/*-----------------------------------------

disable trigger dEmployee on HumanResources.Employee;

disable trigger uEmployee on HumanResources.Employee;

-----------------------------------------*/
Click the window that contains the data generation plan.
On the Data menu, point to Data Generator, and click Preview Data Generation.

The Data Generation Preview window appears and shows a sample of the data that will be generated. If the data meets the criteria that you specified, continue to the next step. If you have to adjust the data generation plan, make the appropriate changes, and then repeat this step.
On the Data menu, point to Data Generator, and click Generate Data.

The Generate Data for Target Database dialog box appears.
In Target Database, click the connection to the AdvWorksSandbox database, and click OK.
When you are asked "Do you want to clear the contents of the selected tables before inserting new rows?" click Yes.

Data is generated according to your specifications. When generation has completed, a green check mark appears in each row to indicate that all data was generated successfully. If data was not generated, errors appear in the Error List window.
Click the T-SQL editor window.

In the T-SQL editor, replace the existing statements with the following T-SQL:

ENABLE TRIGGER dEmployee on [HumanResources].[Employee];
ENABLE TRIGGER uEmployee on [HumanResources].[Employee];
GO

On the T-SQL editor toolbar, click Execute SQL.

On the Messages tab, the following appears:

/*-----------------------------------------

enable trigger dEmployee on HumanResources.Employee;

enable trigger uEmployee on HumanResources.Employee;

-----------------------------------------*/

The triggers are now enabled.

To verify the data generation plan results

On the Data menu, point to T-SQL Editor, and click New Query.

The Connect to Database dialog box appears.
If a connection exists for the database in your isolated development environment, go to step 8.
Click New Connection.

The Connection Properties dialog box appears.
In Server name, type or click the name of the database server to which you deployed the database, such as (local)\SQLExpress.
In Log on to the Server, click Use Windows Authentication.
In Connect to a database, click Select or enter a database name, type AdvWorksSandbox, and click OK.

The connection is created and added to the list in the Connect to Database dialog box.
In the Connect to Database dialog box, click the connection string that corresponds to the database in your isolated development environment, and click OK.

The Transact-SQL (T-SQL) editor appears with an active connection to the AdvWorksSandbox database.
In the T-SQL editor, type the following:
```
SELECT * from HumanResources.Employee;
```
Press F5 to run the query.

The results pane appears and displays the results of your data generation.
On the File menu, click Close to close the T-SQL editor.

You have now defined a database project, imported the schema from a production database, and specified data generation rules to provide realistic test data. You can now put the solution under version control to make it available to the team.

Make the project available to the team

To add your project to version control

In Solution Explorer, click the AdvWorksSandbox node.
On the File menu, point to Source Control, and click Add Solution to Source Control.

At this point, you interact with the installed version control software. This walkthrough provides steps to add your project to Team Foundation Server. If you want to use different version control software, substitute the equivalent steps. If you are using Team Foundation Server, the Connect to Team Foundation Server dialog box appears.
In Connect to a Team Foundation Server, click the server where the team project to which you want to add your solution is found.

Note

If you do not have a team project to which you can add the database project, see Managing Team Projects in Team Explorer.
In Team projects, click the team project to which you want to add the database project, and click OK.

The Add Solution AdvWorksSandbox to Source Control dialog box appears.
Click OK to accept the default values.

Your database project and the files that it contains are put under version control. Initially they are still checked out. You must check them in before other team members can access them.
On the View menu, point to Other Windows, and click Pending Changes.

The Pending Changes window appears.
In the Comment field, type Initial database project creation, schema import, and data generation.
In the Pending Changes window, click Check In on the toolbar.

The Check In Progress dialog box appears as the database project and the files that it contains are checked in. The icons in Solution Explorer update to show that the files are checked into version control.

Note
If you do not have a team project to which you can add the database project, see Managing Team Projects in Team Explorer.

Next Steps

With the database project checked in to version control, the next step is to establish some quality assurance measures before the team starts to make changes. In the next introductory walkthrough, Walkthrough: Establishing a Baseline for the Isolated Development Environment, you create unit tests and build, deploy, and run them in your isolated database development.

To create a database project

To configure your project and import a database schema

To examine the resulting project

To deploy the project to your isolated development environment

To create a data generation plan

To restrict data generation to the tables of interest

To define the data generation plan for the StateProvince table

To define the data generation plan for the Contact table

To define the data generation plan for the Employee table

To adjust the number of rows being generated

To preview the data generation and generate test data

To preview the data generation and generate test data

To disable and enable triggers in the Employee table around data generation

To verify the data generation plan results

To add your project to version control

Tasks

Concepts

Other Resources