Walkthrough: Creating an Isolated Database Development Environment

Article
08/23/2010

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 the objects that are in the production database. You create the project, import the database schema from a production database, and configure project settings. Next, you create a data generation plan. Finally, you 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 Visual Studio Team System Database Edition and have access to the AdventureWorks2008 sample database for SQL Server 2008. To add your project to version control, you must have installed version control software, such as Visual Studio Team System Team Foundation Server or Visual SourceSafe.

For information about how to install the AdventureWorks2008 sample database, see this page on the Codeplex Web site: Microsoft SQL Server Product 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 SQL Server 2008.
In the Templates list, click SQL Server 2008 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 will 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.
Verify that A database project to manage changes to a user-defined database is selected.
Under SQL Script Files, verify that By Schema is selected, and click Next.

Important Note:

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

Note

You can change these options after the project has been created. For more information, see How to: Configure Database Properties for Database Projects.
On the Import Database Schema page, select the Import existing schema check box.
In the Source Database connection list, click the connection that corresponds to the server and your AdventureWorks2008 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 Database Objects and Settings.
Accept the default options for importing objects and settings, and click Next.
On the Configure Build and Deploy page, in Target connection, click the Edit 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 the Default deployment collation list, click Use the collation of my project.

This step makes the development environment for the target database use the same collation as the database project.
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 to be lost.
Click Finish to create the database project and import the database.

Your database project is created. This process might take a few minutes.
Review the summary results, and click Finish.

In the background, Database Edition is still analyzing your database schema. In the status bar, a message appears that displays the number of operations that must complete before your database schema is fully analyzed.

Next, you will examine the database project that you created.

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

To examine the resulting project

In Solution Explorer, expand the AdvWorksSandbox node, and expand the Schema Objects child node.
Explore the subnodes 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.
On the View menu, click Database Schema View.
In Schema View, expand the AdvWorksSandbox node.
Explore the subnodes 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 Deploy 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 he or she wants 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.
If you will deploy this project to the same database server that is running the original AdventureWorks2008 database, you must change a file name to avoid conflicts. If you instead deploy to a server that does not contain the original AdventureWorks2008 database, you can go to step 7.
Expand the Schema Objects folder, expand the Database Level Objects folder, expand the Storage Folder, and expand the Files folder.
Double-click FileStreamDocuments.sqlfile.sql.

The file opens in the Transact-SQL (T-SQL) editor.

Modify the definition of the file to match the following:

ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [FileStreamDocuments], FILENAME = '$(DefaultDataPath)SandboxDocuments') TO FILEGROUP [DocumentFileStreamGroup];

On the File menu, click Save FileStreamDocuments.sqlfile.sql.
On the Project menu, click AdvWorksSandbox Properties.

The Project Properties window appears.
Click the Deploy tab.
In the Deploy action list, click Create a deployment script (.sql) and deploy to the database.

Important Note:

If you do not change the deploy action, you will only generate the deploy script; nothing will be deployed to the target database.
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 will specify a data generation plan to fill the isolated development database with realistic test data.

Important Note:
If you do not change the deploy action, you will only generate the deploy script; nothing will be deployed to the target database.

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 configure data generation for only 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; therefore, 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 typical 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 and Demographics in the Person table must come from the source AdventureWorks database.
OrganizationalNode in the Employee 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 Categories list, click Data Generation Plan.
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 will modify AWGenPlan 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.Person, 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.
In the Column Details pane, click StateProvinceCode, and set the Generator field to Sequential 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, 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 Person table.

To define the data generation plan for the Person table

Click [Person].[Person] in the data generation plan.
In the Column Details pane, click BusinessEntityID, and set the Generator field to SmallInt.
In the Properties window, in the Generator section, set the Max property to 51 and the Min property to 2.
In the Column Details pane, click PersonType, and set the Generator field to Regular Expression.
In the Properties window, in the Generator section, set the Expression property to (GC|SP|EM|IN|VC|SC).
In the Column Details pane, click AdditionalContactInfo, and set the Generator field to Sequential 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].[Person].
In the Column Details pane, in the Generator Output field for the AdditionalContactInfo, click [AdditionalContactInfo].

This step selects the column in the result set that you want to associate with this column.
In the Column Details pane, click Demographics, and set the Generator field to Sequential 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].[Person].
In the Column Details pane, in the Generator Output field for the Demographics, click [Demographics].

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 BusinessEntityID, and set the Generator field to SmallInt.
In the Properties window, in the Generator section, set the Max property to 51, set the Min property to 2, and set the Distribution property to Normal.
In the Column Details pane, click JobTitle, 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 DateTime2.
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 DateTime2.
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 DateTime2.
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.
In the Column Details pane, click OrganizationalNode, and set the Generator field to Sequential Data Bound Generator. Select the the check box for this column if it is not already selected.

By taking this step, you indicate 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.

By taking this step, you specify 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 [HumanResources].[Employee].
In the Column Details pane, in the Generator Output field for the OrganizationalNode column, click [OrganizationalNode].

By taking this stepm, you specify the column in the result set that you want to associate with this column.

Next, you will 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.
In the data generation plan, click [Person].[Person].
In Rows to Insert, type 25.
In the data generation plan, click [Person].[StateProvince].
In Related Table, click [Person].[CountryRegion].
In Ratio to Related Table, type 2:1.

By taking this step, you specify 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 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 one time. 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 one time, 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, create a connection to the AdvWorksSandbox database, and click OK.
When you are asked "Do you want to delete existing data from tables before you generate new data?", 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 [HumanResources].[dEmployee] ON [HumanResources].[Employee]
GO

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

On the Messages tab, the following appears:

The command(s) completed successfully.
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];
GO
```
On the T-SQL editor toolbar, click Execute SQL.

On the Messages tab, the following appears:

The command(s) completed successfully.

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 in to version control.

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.