When you are working as a data/database architect, sometimes it’s a bit hard to determine/figure out right away if a database table will require an auxiliary index structure to guarantee that queries will always run fast (at least most of the time), or even if the choice you made for a particular Clustered Index will eventually negatively impact the performance of your insert, update and/or delete statements. For that reason, I think there should be a scientific process to get to the conclusion if the decisions around such index structures will benefit performance or not, and that’s where the SQL Data Generator tool for SQL Server comes into play.
As you’ll see during this post, the tool can let you quickly and effortlessly fill your database with enough test sample data so that you can evaluate your test cases.
Installation
You can download dbForge Data Generator by clicking on one of the “Get Trial” buttons. However, I highly recommend clicking the one that says “dbForge SQL Tools Professional Trial” because that will also include the whole suite of SQL Server Management Studio extensions, which is worth every bit.
Note: As you can see, the site states that the tool requires Microsoft SQL Server Management Studio 2012 or higher installed, so make sure that’s the case so that you can follow along.
The very first time you open Data Generator, you’ll see this pretty much empty screen, so let’s click on “New” to get things going:
You are immediately being requested to enter a connection for your SQL Server instance and a database that the tool will target.
Before going any further, let’s first explore what does the “Save Command Line” button at the bottom corner does:
So based on the values that we specify for those parameters, it will build a command-line instruction and will also give us the possibility to save the instruction as an executable .bat file. This can be used for automation purposes, pretty cool if you ask me!
Moving forward, let’s specify a connection to our SQL Server instance then:
Since we haven’t, because we are opening the tool for the first time, then let’s click on “Manage” to create one.
Let’s click on “New:
You’ll see a regular window where you have to specify basic connection string parameters. The cool part is that you can categorize your SQL Server instance, either using any of the 4 presets (Development, Production, Sandbox, and Test) or create your categories (as I already have).
If you click on the “Test Connection” button, a popup will trigger stating that you have successfully connected to your instance, and it will even tell you the SQL Server version detected (if the specified values are the correct ones, of course).
After you succeed with your connection creation, then hit the “Connect” button, and it will now appear listed in the “Connection Manager” window:
Now with your connection selected, the list of databases will be retrieved, and you can select your target database.
On the next screen, you’ll get all the different options to generate your test data. I’d recommend testing the different combinations until you hit the sweet spot for your particular case:
Things will start to get interesting because, for each column of your desired table, you will get several options to concrete your data generation. Let me show you different screens for the same table:
The cool part is that for every option that you tick, you will get a preview at the bottom of the data that will be generated, instead of ticking a ton of checkboxes to realize at the end (and probably after wasting quite some time) that it is not what you intended to do.
You can even mess around with regular expressions - for string columns or a myriad of options available. This is a characteristic that truly sets apart the tool from anything I’ve ever seen before!
Now, when you are comfortable with the set of options for all the columns that you want to populate with data, then click on the green arrow:
In the next screen, you are given the option to execute the data population script against the selected database, save the script file to a location in your machine, or open it within the tool to take a look at it:
Important Note: You’ll notice that the tool will tell you that the trial version only allows the generation of 50 rows per table, so don’t get confused if you’re not getting the thousands of results that you configured.
Regardless of the option you choose, the tool will give you one additional screen to configure the resulting script, along with the option to backup your target database in case you think that your particular use case requires it. In every tool that I’ve reviewed from Devart, I always get a smile on my face whenever I see that they give the option to the DBA to backup the data in case something goes wrong, thanks Devart!
Before getting to the final step, you’ll get a warning if you selected that you want to truncate your table. That’s a very user-friendly approach because the end-user could probably tick the “truncate data” checkbox by mistake, choose not to backup the data, and have the perfect recipe for an awful day (with several roundtrips to the bathroom for sure).
Besides any warning, you will also be able to see the action plan that the tool intends to execute:
The final step will be the output of the option you previously selected. In my case, I wanted to view the generated script in the built-in editor:
So after clicking “Execute”, I can see the backup file successfully generated, and as advertised, my 50 sample records are right there in my database:
Final thoughts
● As we saw during the development of the post, the trial version of the tool will put a cap on the amount of sample data generated. However, the full functionality, in my opinion, is worth every penny!
● A tool like this one can ease a lot of the process of testing how your database server will serve the required queries against databases that have grown to a large scale; this will allow you to implement a strategy well in advance and adopt a proactive measure instead of a reactive one.
● If you want to get a deeper dive into the tool, you can check the corresponding official documentation center.
● I’m not going to lie, and I had a jaw-dropping moment when I saw all the options available to populate each specific field. It’s the first time I’ve seen something like this, so a round of applause for the Devart team!
Top comments (0)