This section guides you through a worked data generation example in SQL Server. It includes steps to create an empty target database, which is then used in the data generation example. It provides links to further examples and to resources to understand how the data generator works.

Contents

Preparation

Preparation of Data Generator

Please do the following before beginning the worked example

1. Install the data generator CLI.
2. Verify your installation by running the following command in a terminal window:
   1. Windows cmd: datagenerator.exe --version
   2. Windows PowerShell: ./datagenerator --version
   3. Linux: ./datagenerator --version

The data generator should report its version number as below (example in Windows cmd).

C:\DataGeneration\>datagenerator.exe --version
0.2.0.0

Preparation of Database Engine

Microsoft SQL Server comes with a command-line SQL query tool, sqlcmd. It can be found under the directory where you have installed Microsoft SQL Server, in Windows this is typically C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn. You can run sqlcmd by opening a command prompt directly in this directory, or add the directory to your PATH environment variable to enable running sqlcmd from any where on your computer.

Enter sqlcmd in a command prompt and you should see the following:

1>

Now you have entered sqlcmd and you should be able to execute SQL queries against the databases you created. To exit sqlcmd, simply type:

quit

Create a target database

Create an empty database

Create an empty database called TargetDatabase in SQL Server.

You can do this using a graphical tool like SQL Server Management Studio (SSMS).

Alternatively you can do this by executing this SQL query below, either in SSMS or in sqlcmd.

CREATE DATABASE [SourceDatabase]; 
go

Populate the database

Download the SQL script sql-server-sample.sql and execute it in the new database. 

Again, you can do this using SSMS, or you can execute the command below at a command prompt (NOT inside sqlcmd): 

sqlcmd -d TargetDatabase -i sql-server-sample.sql

This will create a sample database with four empty tables. It represents a simple social media website, where users create posts and comment on posts, and users belong to organizations.

Check the database contents

Check that the tables exist and are empty. Use SSMS, or use a SQL query as below which lists all the table names:

SELECT TABLE_NAME FROM TargetDatabase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
go

And a SQL query as below which to list the data in a table:

SELECT * FROM TargetDatabase.dbo.Users;
go

Run the data generator

The commands below run the data generator. If the commands cannot run successfully, you may need to work through the connection strings section below to configure them to your environment.

Click on the sections below to view the example command lines:

./datagenerator.exe --database-engine sqlserver `
--target-connection-string "server=localhost;database=TargetDatabase;trusted_connection=yes;TrustServerCertificate=yes" `
--rows-to-generate 1000

datagenerator.exe --database-engine sqlserver ^
--target-connection-string "server=localhost;database=TargetDatabase;trusted_connection=yes;TrustServerCertificate=yes" ^
--rows-to-generate 1000

Connection strings

You may need to change the connection strings to suit your environment. The connection strings identify the source and target database.

The connection strings in the example above use Windows security. An alternative is to set trusted_connection=false , and use uid=username  and pwd=userpassword to specify a username and password with SQL Server's integrated security.

We have further advice on connection strings in our troubleshooting and known limitations section. There is also a lot of information online about connection strings. Here are some tips:

• Use server to specify the SQL Server instance containing the databases. In the example above we are using localhost to connect to an instance running on the same computer that is running the data generator.
• Use appropriate attributes to connect to the instance in a secure way. The example uses TrustServerCertificate to connect to a locally-hosted instance of SQL Server which uses a self-signed certificate. This may not be appropriate in your case. You may wish to ask your IT department for advice.

Output from the command

The command will produce detailed output similar to that shown below. The details may vary. Check that the last line of output says Data generation completed.

Check the data in the target database

Check the contents of the tables in the target database. You should find that each table contains 1000 data rows.

Congratulations! This shows that the data has been generated successfully.

Further examples

The documentation on configuration files explains the formats and provides worked examples of their use, that build on this worked example. 

Learning more about how the data generator works

Now that you have carried out the worked example, you might like to understand more about how the data generator works. Please consult the page Using the worked example to understand the data generator. This page uses the output and the commands from the worked example to explain how the data generator produces referentially valid data.