This is the third and final part of the Database Projects in Visual Studio 2010 –series. In this post I’ll show you how to copy and generate sample data for testing purposes.

Earlier parts can be found from here:

• Part 1
• Part 2

Copying sample data with VS2010 Data Compare

When you are working with a copy of an existing database, an easy way to get good data for testing is copying it from the test or production database. This is fine, if the data isn’t really confidential or contains gibberish, anyway.

There are some quite important requirements, before Data Compare can be used:

• Table and column names and types must match and be compatible
• Primary keys and unique indexes/constraints must match
• Views must have the same unique, clustered index

In practice, it works the best when the schemas are fully matching – at least for the tables that we are interested in copying.

If the source database is really big, copying all the with the Data Compare may not be the best option.

Data Compare works the best, when the source database (i.e. a test database) is small enough and needs often synchronizing. Copying the whole database can be easier and faster with a full backup or by copying the database files when the database is detached.

Data Compare is very easy tool to use: select Data | Data Compare | New Data Comparison… 

You should get the following dialog:

• Source and Target Database –connections are quite obvious
• Data Compare Options affect greatly what the tool will do:
  • Different Records copies rows with the same keys, but different data to the Target
  • Only in Source copies missing data to the Target 
  • Only in Target deletes the rows from the Target that do not exist in the Source.
  • Identical Records includes also those rows that are identical in both Source and Target (keys and data)
• Once the options are set (I’ll just keep the defaults), advance to the next page, where you can choose which tables you want to copy. All the tables are selected by default.
• After you click finish, Data Compare does the comparison and displays the differences. It won’t copy the data yet:

• You can still deselect the tables or rows, after you see how many will be affected.
• Finally, from the toolbar you have the option to export the script to the editor or file, which might be a good idea to save for resetting the database later in testing scenarios. You can also write the updates immediately.
• Changed rows are easy to synchronize by re-running the tool.

Generating Test Data with Data Generator

If you don’t have the luxury or need to copy real-life data for testing, you can always generate it with the Data Generator tool.

I can think of at least these scenarios:

• Filling empty databases with typical data
• Generating large amounts of data for load testing, query and index tuning
• Generating varying types of data to test edge-cases like Unicode, special characters and even badly formed data

To get started, you’ll need to create a Data Generation Plan. You can create multiple plans for different scenarios.

• You can right-click in the Solution Explorer on Data Generation Plans –folder of the Database Project (see Part 1). Select the Add | Data Generation Plan… and choose the Data Generation Plan –option.
• The tool uses automatically the current schema of the Database Project and creates a basic plan.
• The designer window appears, where you can adjust the parameters. I’ll explain it in the next section more closely.
• If you then select Write Updates from the toolbar and choose a destination database, it’ll generate the data with default parameters. It’ll look something like this:

• As you can see, it isn’t very easy on eyes, but it’s better than nothing.

This was the simplest way to use the tool, but you can tune the generator to make it generate exactly the kind of data you want.

Customizing VS2010 Data Generator parameters

• Open the designer screen, where you can adjust the parameters:

• The most interesting parts are:
• Rows to Insert column in the upper part (should be obvious)
• Generator in the lower, column-specific part
• The generator-column allows you to tell, what kind of data you want to generate. For example, you can use regular expressions to define patterns:

• With regular expressions, you can easily generate usually needed types of data like e-mails, IP-addresses, postal codes etc. You can find ready-made samples from RegExLib.com.
• With Preview –window (toolbar or menu) you can see what the outcome will be before running the tool.
• Here is an example with this regex in the CompanyName –column: 
  ([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$

This should help you get started with the Data Generator-tool.

Conclusion

In this post I explained how to use the Data Compare and Data Generator to utilize them in real-life needs.

This post ends the current series, but there are still lot’s of things in Database Projects to write about later. If you have any questions about these tools, write a comment or give a nudge at the Twitter.

This continues the series of posts about the Database Projects in Visual Studio 2010. In the first part I introduced you to the Database Project. In this part I’ll explain how Schema Comparisons can be used to synchronize database schemas.

Why?

During database projects there are often (at least should be) multiple copies of the same database, for example:

• Production
• Testing
• Multiple development copies

When a change to the database structure is made, it’s important to synchronize the changes in a managed way. Schema comparison is a powerful tool, that allows you to copy the changes, no matter where they were made.

Creating a local copy of the database for developer

Let’s say you are a new developer in a project. You have just pulled the project out of the version control and have the latest offline copy of the project’s database in the Solution Explorer. Now you want a local instance of the database for development and testing purposes.

• Add a new Schema Comparison to the database project by right-clicking on the project and locating the Schema Comparison… under the Add -sub-menu. Select the item and Add:

• Immediately, another dialog appears where you can select the Source and Target –databases:

• The interesting parts are the Project, which contains the offline copy of the database and the Database –part, which points to a new or existing database connection in SQL Server. The changes can be synchronizes between any of these.
• In this case, however, we want to create a local copy of the database in SQL Server from the offline copy. This means that the Source Schema should be Project (NorthwindDB in this case) and the Target Schema should be a new database connection.
• Select New Connection… and enter the connection details:

• I have a local, named SQL Server 2008 R2 instance called “.\SQL2008R2”, so I point to it. As the target database doesn’t exist yet, I have to type the name manually (“Northwind_dev”, for example).
• After pressing “OK”, Visual Studio warns the db doesn’t exist, but after confirmation it can create one.
• You can also create an empty database in Management Studio and connect to it, if you want better control with how and where the database will be created.
• Now that the connection details are in place, Press OK.
• Optional: Before selecting OK, you can set Schema Compare Options with the Options… –button. In addition to the default values, I ignored SQL Files (filename conflicts due to an existing same DB), Role Memberships and Users.
• Press Ok, until you get this window with Schema Comparison results:

• As you can see from the screenshot, you can now review all the changes (Create/Update/Drop) and skip them, if you want.
• From the toolbar you can either Export the whole script to a Query Editor or file or Write Updates directly, which I’ll select this time.
• After executing the Write Updates, I have now an empty copy of the database in my local SQL Server.

Synchronizing Schema Changes

Now that you have a copy of the database, what happens if any schema changes? For example, if you have to modify columns in the development database due to requirements, you need to propagate the changes to the project and later to the test and production databases.

Let’s say I add a new table called “News” to the Northwind_dev –database and want to bring the changes to the offline copy.

• First, I’ll do the changes directly to the database using the Management Studio:

• Then, I’ll open the Schema Comparison –window (by double-clicking the .scmp-file in the Solution Explorer) and define the new parameters: Source: Database/Northwind_dev and Target: Project/NorthwindDB. After clicking OK, the comparison finds the new table:

• After writing the updates, you can see a new script file in the Solution Explorer:

• Now you can check-in the modified database schema along with other files in the changeset.
• The modified database can then be picked up by other team members and changes can then be applied to other databases.
• You could also write the changes to a script file and hand them to the DBA, who can then run the modifications on test and development databases - after validating the scripts.
• Schema Comparison works also between SQL Server/database instances directly, so you don’t necessarily have to maintain offline copy of the database schema. However, keeping it in the version control makes tracking and canceling changes later much easier.

Conclusion

This post was about Schema Comparison and how the tool can be used to copy databases and manage the schema changes. Synchronizing the offline copy allows you to version control the changes in a managed way.

In the next part I’ll tell you how to copy data with Data Compare and how to generate sample data.