Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Red Gate's SQL Data Generator

I'm reading SQL Server MVP John Magnabosco's new e-book, Protecting SQL Server Data, and he includes a database schema to be able to use his examples. The schema doesn't include data and in the instructions he makes reference to Red Gate's SQL Data Generator. To this point I have had SQL Data Generator installed as part of the Toolbelt, but I had used it. After a quick run through, I'm pleasantly surprised. In cases where it can clearly identify the fields, it generates very realistic data, like so:

However, if it can't identify the data, it can come up with some very interesting results, such as the number of years a borrower has owned the residence:

The good news, however, is that you have the ability to determine how the data is generated. The column to the left of Own_Years is Rent_Years and if you notice, the numbers look fine. That's because I've modified the data generation to only allow values from 0 to 10 years:

For text fields, you can build Regular Expressions to generate the data, if the generators (rules to build data based on the type of information that is needed) provided aren't sufficient.

All in all, I was able to generate working data in a matter of minutes, and it was relatively painless to do. I'll have to play with the tool a bit more in the near future, but I'm impressed with how easy it was to build something that looked fairly realistic and at the initial intelligence to determine what type of generator should be used.

 

Comments

Posted by JohnMagnabosco on 3 October 2009

I am a huge fan of this product. It is also valuable for populating testing or training environments with artificial data.

Posted by Phil Factor on 6 October 2009

SQL Data Generator has a plug-in architecture. As well as writing C# plug-ins, which is quite simple to do, (www.simple-talk.com/.../article.aspx ) It is also possible to create your own generator in SQL Data Generator, by embedding a dynamic language into the application, which can give you more control over the type of data that you want to generate. Users can dynamically generate the type of data they wish based on their own requirements, using IronRuby or IronPython. See www.simple-talk.com/.../article.aspx

I realise I'm not entirely objective on this point but, if I had to choose just one 3rd party SQL tool for a SQL Server database developer, it would be this one. I would never want to be without it. One reason for this is that when Red-Gate built this tool, they took the trouble to ask me exactly what I wanted in a SQL data-generator for developing databases. When developing SQL, it is so good to be able to generate huge tables of data that is as close as possible to the production data. (SQL Data Generator: A Case Study: www.simple-talk.com/.../article.aspx ) It very quickly shows you where you have gone wrong, and what parts of your code needs refactoring. I've always felt uneasy about using real data in development work, and this get around the problem.

Posted by Charles Kincaid on 6 October 2009

Although the area code restrictions are somewhat relaxed the postal regulations are not so much.  I was flat out surprized when the Zip Code (tm) data did not match the state code.  Does it do this with postal codes in the UK?

I gues that rows like that are useful to test your data scrubbers.  Still I like the tool and have reccommended it.

Posted by Mark Horninger on 7 October 2009

Don't know man.  We tried to use it a few months back (the demo version) to fill up some pretty large tables in testing and it kept locking up.  

Seemed like it wasn't quite ready for release.... ended up using it in chunks for the large tables and worked ok for the smaller (< 100000) tables.  for the really complex stuff I ended up just writing SQL script to do it.

Think it's a good concept, but not quite ready for prime time yet.  

Posted by Anonymous on 9 October 2009

Pingback from  Now A Friend Of Red Gate &laquo; Marlon Ribunal&#039;s Note On SQL Server

Leave a Comment

Please register or log in to leave a comment.