Voice of the DBA

Tag: Redgate

A Quick Test Data Manager Eval with My Database Backup

I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and it was a great post. In that one, the sample database Northwind was created and used. However, Alex Yates has modified the scripts to work with backup files, and I’ll show you how easy this is in just a few minutes.

This is part of a series of posts on TDM. Check out the tag for other posts.

The Setup

I’ve filtered my SSMS to only show databases with BB in the name. You can see I have none.

I also have a backup file of a baseball database on my d: drive. My local instance has access to this folder as I use for backups and restores in dev/test work.

While I can pass these parameters in, it’s easy to just change the values in the file after cloning the repo. This way it’s easy to see what’s going on.

That’s it, now let’s fire up PowerShell.

Running the Eval

When I run the file, I see it start up and report the various values. You can see that it’s set the base database name to “BB” and I should see the two databases with the suffixes created. I also see my backup path.

This runs and in a few minutes, I see that the databases have been created and we are ready to subset.

Checking SSMS with a refresh, I see the databases.

If I type “y”, the subsetter runs, and very quickly. This isn’t a massive database, but it is thousands of rows, which makes it easy to play with.

If I run counts, I see this. The left is the full restore, which has 16k records. The subset, on the right, has about 10% of that, with 1644 rows. Pretty cool. So far, this has taken less than a couple of minutes.

Now let’s continue to press “y” and get the classification, mapping, and masking done. Two tables were found with PII (names) and masked.

If I query the tables, I see the results below. Notice that not all values were moved, as the first ID in the subset is 11, but we can see IDs 11 and 22 were masked.

This was a very quick look at running an eval with my own database backup, not a sample db. We’ve had a few people ask to do this for their own testing, and we modified the scripts to work with backups.

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:

January 29, 2025
Monday Monitor Tips: Using the PowerShell API
Redgate Monitor has grown tremendously from its early days and I find many customers using this to monitor lots of servers, like thousands. In those cases, some of tasks you might do to manage your Redgate Monitor server can be cumbersome in the GUI.

This post shows you how to get started with PowerShell to administer your Redgate Monitor Instance.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

The Purpose of the PowerShell API

Redgate Monitor (RGM) has lots of configuration options that you can set. In any size estate, this can be overwhelming when you need to change settings for some servers, but not others. For example, suspending some monitoring for servers being patched or upgraded. While this can be done easily in the web interface, for more than 1 or 2 servers, you might want a programmatic way to do this.

The PowerShell API is designed to help you change your configuration in a programmatic way rather than clicking through the GUI. If you look at the main documentation screen below, it says the same thing.

Getting the PowerShell Module

To get the various PoSh modules you need, you need to go to your configuration screen and click the download under the PoSh API section. You can see this below, and it’s also in the docs.

While this downloads, you also need an authorization token from your install. This is also on the configuration screen and described in these docs. The PowerShell API section has the Authorization Tokens section. Click that and in this screen you can genernate a new one. Save this, as you can’t see it after generation.

From here, test making a basic connection to your server with a script like this one below. One of our solutions specialists wrote it, and it imports the module, sets the URL and token, and then tries a connection. If that works, you are connected. You can run the Get-RedgateMonitorMonitoredObject as well. This gets a list of all objects being monitored.

Where to go from there is up to you. What types of things do you find yourself changing in Redgate Monitor? If there are a series of stops, then think about automating them. We have a set of example scripts that might give you some examples. There are things you might do from a list of servers like:
- add to a group (or move)
- suspect monitoring
- annotate them with an event
- set a tag
- copy settings from one server to another
With PowerShell, there are so many ways to program a script to do the thing you used to do manually. Give it a try with Redgate Monitor today.

https://youtu.be/eTlg1auKRUA

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.
January 20, 2025
Friday Flyway Tips: Chaining Commands for State-based Deployments
One of the cool things I’ve seen with the new Flyway CLI is that I can combine multiple actions together in one call, which can make the process of writing automations streamlined.

I’m not sure I love this, but I’ll show how this works.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Making a State-Based Deployment from the CLI

In a previous post, I showed how to use the new prepare and deploy verbs to create a deployment script and then apply it to a database. This is a great way to handle simpler deployments if you want to work in a state-based fashion with Flyway.

That post showed two different CLI calls, which are shown below:
```
flyway prepare -prepare.source=schemaModel -prepare.target="env:qa" -prepare.scriptFilename="deployments\FWState__deployment.sql"flyway deploy -scriptFilename="deployments\FWState__deployment.sql" -environment=qa -executeInTransaction=true
```
Easy enough, and I like this structure as I’d likely build a pipeline that looks like this, where I prepare the script, then have a manual approval step to check this before deployment:

However, you might argue that someone would check the script in a QA or other deployment, so maybe you streamline things later, or you use this as a QA/test deployment and want everything to run smoothly after a PR.

In that case …

Chaining Commands

I can run this code instead.

flyway prepare deploy …

Let’s see this work. If you look, I have a couple of changes that I can see in Flyway Desktop. There is a table alter and a new stored procedure.

I can streamline the automation of these two changes in the CLI with the code style above with this code:

flyway prepare deploy -prepare.source=schemaModel -prepare.target=”env:qa” -environment=qa -executeInTransaction=true

Here is the pre-execution look at my database:

The execution, which didn’t use a transaction. I’ll have to figure that one out:

And the post database view with the table and proc changes deployed.

I built and deployed the script in one command, and it also appeared to have added my script to the deployments folder. No idea why that happened either.

If you want commands chained, you can do it.

Summary

This post showed how you can chain your prepare and deploy commands together in one command, which might be preferable in some situations.

I don’t know if I like this, or would do it, but it’s not a bad idea. Ultimately, I’d really like this deployment script to be saved back to the repo so I could track it, but that’s going to be some work, and likely that means not chaining commands.

Flyway is an incredible way of deploying changes from one database to another, and now includes both migration-based and state-based deployments. You get the flexibility you need to control database changes in your environment. If you’ve never used it, give it a try today.
January 10, 2025
Using Flyway Prepare for State-Based Deployments
One of the neat enhancements made to Flyway was the addition of state-based workflows and tooling. A lot of people have loved SQL Compare or SQL Source Control for deployments. As I’ve worked with customers, they’ve asked for this, and the v11 version of Flyway includes this for the Enterprise edition.

This post will look at using these two commands to deploy a change to a database from the command line instead of Flyway Desktop.

If you want to know more about Flyway, all my posts are together in one feed.

My Current Environment

I have Flway v11.0 installed and I have two databases: FWState_1_Dev and FWState_3_QA. These two databases are in my flyway.toml file, where I see them configured as development and qa.

I also created a “deployments” folder under my project, which is where I want deployment scripts. I’ll do this manually, but in a pipeline, I’d do this in an automated fashion. As you can see, this is an empty folder.

In Flyway Desktop (FWD), you can see that I have a change made in my dev db that’s ready to deploy. I could click “deploy” and run this from FWD, but I want to experiment with automation here, so I’m practicing at the command line.

I can verify this change isn’t deployed by checking the database.

Let’s get started.

Flyway Prepare

The prepare command is designed to create the deployment script. It can use a variety of sources, but in this case, I’ll use the schema model that I’ve saved in my git repo. This is where I (in general) want to pull from, using PRs and branches to manage work. In this case, I’ll pull from main, where I’ve got that change.

To get my changes, I’ll use a few parameters with Flyway. One thing to note, each of these parameters includes a namespace. I think this will go away at some point, but it’s good to be explicit in any case as we have lots of “source” parameters in different namespaces.

Second, use a single hyphen. That somewhat offends my unix/linux’y self, as the parameter names are more than one character. I’m used to 2 hyphens, which generate an error in Flyway.

The basic parameters I’m using are:
- prepare.source – the location of my source. In this case, the schemaModel defined in my flyway.toml file as “schema-model” in the file system.
- prepare-target – the target to look at to build the script. For me this is an environment preconfigured (see above) as qa, so “env:qa”
- prepare.scriptFilename – the default is D__deployment.sql, but I want it in a subfolder, so I’ll explicitly put this in.
My CLI call is this:

flyway prepare -prepare.source=schemaModel -prepare.target=”env:qa” -prepare.scriptFilename=”deployments\FWState__deployment.sql”

When I run this in my project folder, I see these results:

If I look in my deployments folder, I see the file:

The contents of the file are here:

This looks like what I’d get in FWD if I clicked “Deploy”.

This step just created the file. I need another call to deploy this.

Flyway Deploy

The Flyway Deploy command will execute a deployment script against a target. The idea here is that one script is created from a state-based project and deployed with this command.

As with prepare, I’ll use a few parameters here. The ones I need are:
- scriptFilename – the file to deploy. I’ll use the same one as above.
- environment – the name of the environment where I am deploying, in this case, no need for the prefix.
- executeInTransaction – this should default to true, but I’ll be explicit as I might change this in different platforms (postgreSQL, Oracle, etc.) and so if someone copies this pipeline, I want this to be clear.
This gives me this command:

flyway deploy -scriptFilename=”deployments\FWState__deployment.sql” -environment=qa -executeInTransaction=true

When I run this, it works.

I can verify this in SSMS.

I’ve deployed changes, and if I look at the FWD deploy tab, I see no changes.

I could put these two commands in a pipeline and have them work in an automated fashion.

If you do that, use variables in your pipeline, or ensure people commit (and merge) changes to the flyway.toml file that is used for the various options.

Summary

This post showed a quick way to start automated state-based deployments in Flyway, using the new prepare and deploy commands. A few options were used to control these deployments from a command line.

There are more options that you can include to control deployments and I’ll look at some other ways of doing this in the future. For now, this gets my deployments working easily.

Flyway is an incredible way of deploying changes from one database to another, and now includes both migration-based and state-based deployments. You get the flexibility you need to control database changes in your environment. If you’ve never used it, give it a try today.
January 8, 2025