Walkthrough: Creating and Running a Data Generation Plan

Article
06/26/2007

In this walkthrough you create a data generation plan and run it to fill a test database with randomly generated data. First you create a database project and deploy it to an isolated test database. You can use the isolated database to test data generation without touching your production data or database.

Prerequisites

To complete this walkthrough, you need the following:

Microsoft Visual Studio Team Edition for Database Professionals
Microsoft SQL Server 2005 or SQL Server 2000
Access to a database server that has the Northwind database installed.

Creating the Database Project

First you create a database project and import the schema from the Northwind database.

Note

In a team environment, you might check an existing project out of the source code control system to work on a database project. For more information, see An Overview of Database Creation and Deployment in a Team Environment.

To create a database project

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

The New Project dialog box appears.
In the Project Types list, expand the Database Projects node, and click Microsoft SQL Server.
In the Templates list, click SQL Server 2005 or SQL Server 2000.
In Name, type WalkthroughDataGenerator, and click OK.

A solution is created that contains the WalkthroughDataGenerator empty project. This is your database project. No one else has access to your database project when you work on it.
In either Solution Explorer or Schema View, click WalkthroughDataGenerator.
On the Data menu, click Import Database Schema.

Note

You can also right-click WalkthroughDataGenerator and then click Import Database Schema in either Solution Explorer or Schema View.

The Import Database Schema into Database Project dialog box appears.
In the Source Database list, click the connection that corresponds to your existing Northwind database.

Important

If you have not yet connected to that database, you must first click New Connection to create a connection to it. For more information, see How to: Create a Database Connection.
Click OK.

As the schema is imported, project items that correspond to the objects in the database appear under the database project in Solution Explorer and Schema View.

Note

Even though you connected to the database to import the schema, you are now disconnected and working offline.

Deploying to a Sandbox

Next you deploy the project to a new database. This creates a database that has the Northwind schema, but without the data. This database is an isolated environment (or sandbox) in which you can develop and test the database.

To build the database project

In Solution Explorer, select the database project WalkthroughDataGenerator.
On the Project menu click WalkthroughDataGenerator Properties.

The project properties appear.
Click the Build tab.
Click the button labeled ... to display the Connection Properties dialog box.
Set the connection properties for the database where you want to work, and then click OK.

The Target Connection box is filled with the correct connection string.

Warning

You should create the new database on a test server, a development server, or on your local computer. You should not use your production server.
In the Target database name text box type NorthwindDataGenerator.
On the File menu, click Save All.
On the Build menu, click Build Solution.

The deployment script is built based on the project properties you have just set. You see the status of the build in the Output window, and you should see Build: 1 succeeded or up-to-date as the last line.

To deploy the database project

In Solution Explorer, select the database project WalkthroughDataGenerator.
On the Build menu, click Deploy ProjectName. You can also right-click the project in Solution Explorer and click Deploy.

Warning

You should run this deployment against a test server, a development server, or your local computer. You should not use your production server.

The database project is deployed to a new database. You see the status of the deployment in the Output window, and you should see Deployment succeeded as the last line.

Creating the Data Generation Plan

Next you create the data generation plan. The data generation plan contains the information about which tables and columns you want to fill with data. For more information, see How to: Create Data Generation Plans.

To create the data generation plan

In Solution Explorer, select the Data Generation Plans node.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.
In the Categories pane, click Data Generation Plans.
In the Templates pane, click Data Generation Plan.
In the Name text box, type Products.dgen.
Click Add.

The data generation plan is created. The data generation plan, the Data Generation Preview window, and the Column Details window all appear.

Note

If the Column Details and Data Generation Preview windows are not open, you can open them by opening the Data menu, pointing to Data Generation, and then clicking the window name. By default, the Data Generation Preview window and the Column Details window are docked and tabbed at the bottom of the data generation plan window. To expand your view, click the window and then click Tabbed Document on the Window menu. You can also right-click in the title bar and then click Tabbed Document.
In the Products.dgen designer, clear the checkboxes for all the tables.

Note

You can select or clear all the tables by using the Include All Tables In Data Generation and Exclude All Tables From Data Generation commands. You can access these commands by right-clicking the data generation plan window or, on the Data menu, pointing to Data Generator.
In the Products.dgen designer, select the check box for the Products table.

The Categories and Suppliers tables are automatically selected also. Because the Products table has foreign keys to the Categories and Suppliers tables, you must fill all three tables to fill the Products table. For more information, see How to: Specify Tables for Data Generation.
On the File menu, click Save All.

Specifying Details for Data Generation

Next you specify the details of how you want to fill columns with data. For more information, see Specifying Details of Data Generation for a Column.

To specify details for data generation

Set the number of rows of data to generate by doing the following:
1. In the Products.dgen designer, click the row for the Products table.
2. Click the Related Table column and use the drop-down arrow to select the Categories table.
3. Click the Ratio to Related Table column and type 10:1.
This means that you will generate 10 products for each 1 category that is generated. For more information, see How to: Specify the Number of Rows to Generate.
Set the number of NULL rows of data to generate by doing the following:
1. In the Products.dgen designer, click the row for the Categories table.
2. In the Column Details window, click the row for the Description column.
3. In the Properties window, set the Percentage Null property to 10.
This means that 10% of the data generated in the Description column will contain NULL. You can open the Data Generation Preview window and verify that the Description column contains some null values.
Set the text of the data that is generated by doing the following:
1. In the Products.dgen designer, click the row for the Products table.
2. In the Column Details window, click the row for the ProductName column.
3. Click the Generator column and use the drop-down arrow to select the RegularExpression data generator.
4. In the Properties window, set the Expression property to the following:
```
(Gala|Fuji|Braeburn|Granny Smith|Red Delicious) Apples
```
This means that the data generated in the ProductName column will only contain the name of a type of apple. You can open the Data Generation Preview window and verify that the ProductName column contains randomly generated apple names. For more information, see The Regular Expression Generator.
On the File menu, click Save All.

Running the Plan to Generate Data

Finally you run the data generation plan. After you generate the data, you can use a different tool to log on to the database and verify the new data.

To run the plan to generate data

In Solution Explorer, select Products.dgen.

Note

The data generation plan must also be open. If the plan is not open, open it first.
On the Data menu, point to Data Generator and then click Generate Data.

The Generate Data for Target Database window appears.
In the Target Database drop-down box select the NorthwindDataGenerator database and then click OK.
Click Yes when you are prompted to clear the contents of the tables before inserting new rows.

The data is generated. In the Products.dgen designer the status column is updated with the status of the data generation. The status bar displays a summary of the data generation for all the tables.
(Optional) Use a different tool to log on to the database. For example, you can use Microsoft SQL Server Management Studio or Query Analyzer. View the new data by running the following query:

Note

You can also use the Transact-SQL (T-SQL) editor that is provided in Visual Studio Team Edition for Database Professionals for this step. For more information, see Editing Database Scripts and Objects with the Transact-SQL Editor.
```
use NorthwindDataGenerator

select * from Suppliers
select * from Categories
select * from Products
```
Verify that 50 rows were generated for the Suppliers table, 50 rows were generated for the Categories table, and 500 rows were generated for the Products table. Verify that the Description column in the Categories table contains NULL values. Verify that the ProductName column in the Products table contains only data of the form "<type> Apples". Verify that the types are the types that you specified in the Expression property of the RegularExpression generator.

Security

For more information, see Security of Data Generators.