Wednesday, November 30, 2011

Automate your test data with Red Gate SQL Data Generator

Just about every developer out there has encountered a scenario where they need to load test the application they are creating, but simply cannot come up with enough load to make it happen. I'm not going to go down the rabbit hole that is load testing an application in this post, but I do want to talk about one component of load testing, which is working with a lot of data. I seem to always run into the situation where I'm developing an application for a client, and when it comes time to test the application in a real world scenario and need real world data, the only viable solution is to back up data from a production server and massage it to "fit" into my applications database. Bad idea. Yes, there are times when this is unavoidable, but in general, do you really want to make multiple copies of sensitive data? Also, in most cases I cannot just copy and paste the data into my database. In the past I've created a number of data migration utilities, data population scripts, and used a number of tools just to populate my application with data that I can use to test it. All of this takes time, and effort.

In contrast, there is Red Gate's SQL Data Generator. The SQL Data Generator is aptly named, because that is what it does, it generates data. And good data in fact. I downloaded the product trial recently when trying to hand jam a data population script for over 1,000,000 rows of data in multiple keyed tables. Needless to say, I became a little frustrated and started looking for alternatives. I downloaded the tool to my machine, installed it, and generated 1,000,000 rows of data for all of my tables in under 10 minutes. Nice. In addition to simply generating data, the SQL Data Generator is smart enough to create records that enforce referential integrity, that match data types, and in some cases, even provide data reflective of the column name. In one case, I have a column that is of type VARCHAR named STATE. The data created included all 50 US State names.

Likewise, any string columns that had NAME in the title ended up with names of people for values. Though not always accurate (in one case an "EXTRACT_NAME" was given the value of "Cindy")... but it will do. Coolest of all though, is that any string column that included ADDRESS in the title ended up with true to life address information. Though I doubt you will be able to find any of these places on a map, the fact that you have legible formatted address information is awesome!

Similar to other Red Gate products, you will also find additional tools in your SQL Server Management Studio context menus:

So, in a matter of minutes I was able to populate my database with a million rows of relatively valid data. I was able to open up my app, test page load times as well as the speed of paging, sorting and filtering in grids. I was able to use profiling tools to analyze the performance of my data retrieval methods. I was, more or less, able to load test my app.

All I have to say is work smarter, not harder.

Troy Hunt offers a more in-depth post on the SQL Data Generator here:

No comments:

Post a Comment