I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and a follow up post on using your own backup. One of the things I didn’t show from my own database was that it had no FKs, so the subsetting didn’t quite work as I wanted.
This post shows how to correct things and add starting tables for the subsetter to look at in order to customize your setup.
This is part of a series of posts on TDM. Check out the tag for other posts.
The Setup
When I ran the subsetter PoC with my own backup, I showed this screen comparing the size of the dbo.players table before and after.
Here’s what was missing. Let’s look at the counts from all tables. This seems OK, 10%-ish for most tables. The smaller ones are excepted there.
However, if I look at the subset and my LahmanID = 11, I see the player, but no batting stats.
That’s not useful. We got a random 10% from these tables, and my data isn’t intact. In a larger database, I might miss that I had incomplete, unmatched data across tables and write reports or queries that seemed to work, but really didn’t.
Let’s fix this.
Customizing the Subset
We have the ability to customize the way the TDM tools work by adding in things we know, which can’t be detected. Like FKs between tables that aren’t declared. In this case, I want to add in a relationship.
Note: the best place to do this is in a settings file, which I’ll customize for my purposes.
If I look in the TDM-Automasklet repo, there’s a settings file already setup that grabs certain orders for Northwind.
I’ll change this as follows to just grab all players born in CA. I have no idea how many this is, but let’s filter on that.
Let’s start the TDM-AutoMasklet and just run through the subset. To use my settings file, I’ll alter this line (20) in the file:
Since I added my file to another repo, I’ll put in the full path:
I’ll run the PoC and I get to the subset section. I’ll stop, but I’ll copy the subsetter command, which is highlighted below.
This command is this (broken into lines for clarity):
rgsubset run –database-engine=sqlserver –source-connection-string=”server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes” –target-connection-string=”server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes” –target-database-write-mode Overwrite
However, as of Jan 15, this is missing the options file. I’ll add that to the command, which will now look like this:
rgsubset run –database-engine=sqlserver –source-connection-string=”server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes” –target-connection-string=”server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes” –target-database-write-mode Overwrite –options-file E:DocumentsgitTDM-Demosrgsubset-options-bb.json
When I run this
If I now look at rowcounts, I see this in the original (left) and subset (right) databases. Note that there rather than 10% of most tables, I now see 1800 players, but almost no rows in other tables.
This happens as I’ve selected a starting table, a parent, but since I don’t have declared relationships as FKs, the subsetter essentially had no idea where to go. It filtered based no dbo.players.birthState = ‘CA’, but that’s it.
I can add a second starting table, teams, as well. When I do that, I have this in my options file. Note, I’ve filtered on the years after 2000. If I don’t filter, I get all the values passed through. I think this is because this is a fairly small table and I haven’t specified a target size.
When I run this, it’s again quick and I see only 360 rows moved over in the dbo.teams table.
I could add other starting tables if I had different parts of my database that had unreleated entities. However, in this case, most of these tables are related, just not with explicit DRI.
This post has shown a way to start controlling the subsetting. In the next post, I’ll look at adding in the manual relationships.
Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.
Video Walkthrough
Check out a video of my demoing this below:
