I’m in Austin today, ready for the Redgate Software 25th Birthday celebration. The company started in 1999 and this is their 25th birthday. All of our offices are celebrating, with most of us in the US coming to Austin for a party last night. My wife (and many partners) are here as well, emphasizing that we care about our people and recognize that partners support our employees.
I haven’t known Redgate for 25 years, but I have known one of the founders for 22 years. I first met Simon Galbraith in 2002 at the PASS Summit in Seattle. They were our first advertising customer at SQL Server Central in 2001 and the relationship continues through today. Redgate purchased SQL Server Central in 2006 and I’ve been working for them ever since.
In that time, it’s been interesting how my job has changed and evolved. I remember the early days of SQL Monitor being released and me demoing it from horseback.
There was the DBA in Space promotion, which I was only lightly a part of, but it was fun.
We had the
SQL in the City events, which included a tour around the US.
I still remember the first one in London at the Royal Society of Medicine in London. That was a treat.
and SELECT Star beer.
We ran that series for a long time, with quite a few live and virtual events. It was a fun time for my coworkers to get together in various places in the world. One of our last live events was in Cambridge at the Redgate office, with our 4 advocates.
Now we’ve evolved to the Redgate Summits, one of which is coming to New York next month.
I’ve watched Redgate grow to include Flyway (I’ve got a tips series), SQL Provision, and now Test Data Manager. I’ve done so many blogs and promos, the latest of which is TDM in 10 minutes. One of my favorites was a photo shoot in Cambridge, where they got me on another horse.
It’s been a great time for me and I’ve enjoyed my job with Redgate. I continue to do so today and look forward to the future. I truly hope this is the last job I have.
fitching – v. intr. compulsively turning away from works of art you find frustratingly, nauseatingly good – wanting to shut off the film and leave the theater, or devour a book only in maddeningly little chunks – because it resonates are precisely the right frequency to rattle you to your core, which makes it mildly uncomfortable to be yourself.
Good is relative, I guess. Maybe things are so well done that you can’t handle the imagery?
I’ve found myself fitching while trying to watch Crash many years ago at home. My wife was captivated, but I had to get up and leave the room. It was far too real, and too possible for me, and I couldn’t handle it.
That has stuck with me, and I’ve avoided certain films or books since then.
Test Data Manager (TDM) is a suite of products from Redgate that make it easy to build dev and test databases in seconds. It’s a nice rewrite of a number of pieces of technology that we have sold for years, and it was launched at the PASS Data Community Summit in 2023.
I’ve been working with a few customers and sales engineers as they evaluate the fit for TDM in their environment. TDM is great once it’s running and can create a lot of agility for development teams as well as help them build better tested, higher quality software.
One of the challenges we’ve found is that the setup can be complex, and the knowledge required to get up to speed is high. There are a lot of moving parts to get this working in a way that makes it seem simple for the end users (usually developers).
In the spirit of ingeniously simple software, one of our engineers, Alex Yates, built a mini-PoC system using PowerShell that’s available. This post shows how you can get started to demo subsetting and masking in less than 10 minutes.
There are really a few things you need, but the tool does most of the work:
dbatools
Redgate tools (and a Redgate account)
git
make sure c:\temp exists
If you have dbatools installed, we import the module, if not, we download and install it for you. For the tools, we’ll download them and start a trial or you can get these tools and a license from your account executive. These tools run on various platforms, so ping your rep or sales@red-gate.com.
You should have git installed, and temp should be there.
Git makes this super simple. Just clone this down.
Configuration
There isn’t much to configure. In the repo, open run-auto-masklet.ps1 and look at the first 15 lines. These are where you might change things.
The local instance is set in line 2. If you have a named instance, use that. I’ve included a config file below that I used on a named instance.
That’s it. The repo includes a copy of Northwind in an install script to create the full sized database as Northwind_FullRestore. The subsetter will then move a portion of data to Northwind_Subset. If you want to change these names, you can do that.
Running the Tool
This tool can run run as a normal user, but if you need dbatools installed (it will do that), then it needs to run as Admin. I added that as a requirement above, so you don’t need to run this as an admin.
Here’s what the tool does:
Get dbatools
Get the latest versions of the subsetter and anonymize.
authorize you, and start a trial if a license isn’t assigned to your Redgate ID.
drop the two databases (Northwind_FullRestore and Northwind_subset by default)
Creates the two databases with schema (and data for the full restore)
pauses with output
runs the subsetter to move a portion of data to the Northwind_Subset database
pauses
runs the classification process against Northwind_Subset to classify columns
pauses
Runs the masked against Northwind_Subset to mask data
Here’s the first set of output, showing the config and first part of the process.
Here is the first pause. You can see there are db create notes and then an explanation of what to see:
I like that this gives the subset command, which takes some getting used to. The TDM GUI hides this, but every customer has wanted to customize things, so this is a helpful way to do the PoC.
The subsetter does a lot, as you can see below, but basically it map out the database and then starts to determine which data needs to move. In this case, lines 10 and 11 of the source scripts shows that we are subsetting dbo.Orders with the OrderID<10260.
When this is complete, we get another message that explains what happened. We get some telemetry as well with the time taken here.
We also see the next part of the process, which is classifying the data. Again, we see the command for this, and you see this runs quickly.
Lastly, the next pause tells us there is a classification file at a particular location. We get the path if we want to look or edit the file.
Then the masker runs, and we see that 5 tables are masked. We get telemetry and below the results you see, there is more info on what’s happened and what to look for in the databases.
Checking the Tool
Once the execution is complete, I decided to look at the two databases. In SSMS, I had a vertical tab group to compare things.
First, subsets. I’ll count orders, order details, products, and employees. You can see the original db on the left and the subset on the right. Less data.
Not super impressive, but imagine there were a factor of 1000 on the left. That would be cool.
What about masking? Let’s check.
The Shippers, Suppliers, Employees, Customers, and Ordere tables were masked. Let’s look at Shippers. We can see the phone number is masked.
Checking Employees, I see less as there is a subset here, but I see data masked.
You should see similar results, and what’s more, you can alter the various config files or filters to test how your changes work.
I’m a big believer in sandboxes for learning and experimenting. This gives you a nice sandbox. You can change the various files or script and then re-run the tool in a couple minutes to see your changes.
Here are the CLI docs you might use to change things:
I almost missed this month, so this is also a good #SQLNewBlogger post. I thought about it for a few minutes as I ate breakfast at my desk and then knocked this out.
This is the monthly T-SQL Tuesday blog party. I manage the site at tsqltuesday.com, trying to keep the party going. I have a lot of help from hosts each month running the topic, and I appreciate their efforts. Join in an write, and then host a month. Lots of people have done it.
Past Advice
I struggle with this, as I’ve had a great life. I wouldn’t change anything, given where I am today, 33 years after my first data job. However, knowing I’m not changing my life in some time travel way, this is something I wish I’d have known about in my early 20s.
Network and help others in the community.
I’ve done this lightly in my first few jobs, but mostly within organizations. As I’ve grown and changed jobs, I’ve seen tremendous power in people getting together to talk, to get to know each other, to share problems and solutions, to present their knowledge and learnings with others.
User groups were a core part of this, and out of them grew the PASS organization and Summit, the SQL Saturdays, and the amazing community we have. It’ s far different than other communities, and many of them see it as well. They wish their world was like the data platform world.
The power of networking is amazing. The rich world that comes from community is something special.
It’s still there today and it’s worth joining, no matter where you are in your career.
I’m in Austin today, ready for the Redgate Software 25th Birthday celebration. The company started in 1999 and this is their 25th birthday. All of our offices are celebrating, with most of us in the US coming to Austin for a party last night. My wife (and many partners) are here as well, emphasizing that we care about our people and recognize that partners support our employees.
Voice of the DBA 