Data generators for SQL server?

Question

I would like to receive suggestions on the data generators that are available, for SQL server. If posting a response, please provide any features that you think are important.

I have never used a application like this, so I am looking to be educated on the topic. Thank you.

(My goal is to fill a database with 10,000+ records in each table, to test an application.)

Answer 1

I have used the data generator at http://www.generatedata.com/#generator in the past. May be worth a look.

Answer 2

I have used this before

http://sqlmanager.net/en/products/mssql/datagenerator

Its not free though.

Ref integrity checking is quite important, or your tests will be no good without correlating related data.(in most cases)

Answer 3

Something similar has been asked here : http://stackoverflow.com/questions/16317/creating-test-data-in-a-database

Red Gate SQL Data Generator does a great job in that domain. You can customize every field of your database and using random data with seeds. And even create specific patterns using Regex expressions.

Answer 4

Visual Studio Team System Database Edition (aka Data Dude) does this.

I have not used it for data generation yet, but 2 features sound nice:

1. Set your own seed value for the random data generator. This allows you to prodcue the same random data more than once.

2. Point the wizard at a 'real' database and have it generate something that looks like real data.

Maybe these are standard features elsewhere?

Answer 5

For generating sample data, I use simple Python applications.

Considerations:

1. Simple to modify and configure.

2. A repeatable set of data that you can for performance testing and get consistent results.

3. Follow all of the DB referential integrity rules and constraints.

4. Realistic data.

The first two indicate that you want to produce script files that will load your data. The third is tougher. There are ways to discover the database metadata and constraints. Looking at 3 and 4 together, you don't want simple reverse engineering -- you want something you can control to produce realistic values.

Generally, you want to build an entity model of your own so that you can be sure you have ranges and key relationships correct.

You can do this three ways.

1. Generate CSV files of data which you can load manually. Nice repeatable test data.

2. Generate SQL scripts which you can run. Nice repeatable data, also.

3. Use an ODBC connection to generate data directly into the database. I actually don't like this as much, but you might.

Here's a stripped-down one-table-only version of a data generator that writes a CSV file.

import csv
import random

class SomeEntity( list ):
    titles = ( 'attr1', 'attr2' ) # ... for all columns
    def __init__( self ):
        self.append( random.randrange( 1, 10 ) )
        self.append( random.randrange( 100, 1000 ) )
        # ... for all columns

myData = [ SomeEntity() for i in range(10000) ]
aFile= open( 'tmp.csv', 'wb' )
dest= csv.writer( aFile )
dest.writerow( SomeEntity.titles )   
dest.writerows( myData )
aFile.close()

For multiple entities, you have to work out the cardinality. Instead of generating random keys, you want to make a random selection from the other entities. So you might have ChildEntity picking a random element from ParentEntity to assure that the FK-PK relationship was correct.

Use random.choice(someList) and random.shuffle(someList) to assure referential integrity.

Answer 6

I've used a tool called Datatect for this (http://www.datatect.com/).

Some of the things I like about this tool:

1. Uses ODBC so you can generate data into any ODBC data source. I've used this for Oracle, SQL and MS Access databases, flat files, and Excel spreadsheets.
2. Extensible via VBScript. You can write hooks at various parts of the data generation workflow to extend the abilities of the tool.
3. Referentially aware. When populating foreign key columns, pulls valid keys from parent table.

Answer 7

I've rolled my own data generator that generates random data conforming to regular expressions. It turned into a learning project (under development) and is available at googlecode.

Answer 8

I just found about that one: Spawner: http://spawner.sourceforge.net/