Voice of the DBA

Tag: Redgate

Monday Monitor Tips: VLF Alerts
A recent change made to Redgate Monitor to add a new alert for VLF count. This post looks at the change.

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

Tracking Virtual Log Files

Virtual Log Files (VLF) are sections inside of your physical log file (.ldf). These have no fixed size or number per file, but there can be many. The architecture of the log is explained in this doc and it varies according to a number of factors.

That doc also explains there are issues with too many VLFs inside of a log file. There are plenty of other posts about this (Brent Ozar, Kimberly Trip) and it is somethin you want to keep track of.

Redgate Monitor changes and grows every week with new releases and one of the resent releases (14.0.41) included a new alert for VLFs.

To configure this, select the gear icon in the upper right of Redgate Monitor.

On the configuration page, select the Alert settings. This will bring you to the details for your alerts.

There are a number of items on the Alert Settings page, but scroll down to the bottom of the SQL Server Alerts section. The Virtual log file count is the last alert.

The default setting is to raise multiple alerts here. The settings are:
- low: 100
- medium: 300
- high: 1000
These may or may not be appropriate for your system, and for me, I don’t know I’ve ever had time to worry about this and I might disable a low level alert and only have two, but you can decide what’s important to you.

The important thing is that if you worry about VLFs in your environment, you can get alerted and track this over time.

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.
February 24, 2025
Adding Manual Relationships Between Tables in the TDM Subsetter
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.

A previous post showed how add starting tables for the subsetter to look at, however that didn’t get me a good data set for testing. This post continues looking at the subsetter by adding manual relationships to our configuration.

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

Declaring a Relationship in the Options File

In my previous post, I’d picked a starting table and had reduced the dbo.players table from 16564 to 1800. However, I only had player information. If I query my subset database, I see there is a player, but I have no batting statistics for this player.

This is because my table has no declared FKs in it. If I check the dbo.batting table, I can see only a PK.

Let’s fix this.

Declaring Manual FK Relationships

In the options file documentation, there is a section that notes manual relationships can be declared with a key called “manualRelationships”. If I copy/paste the example section into my options file, I’ll see this:

I don’t have a SourceTest table, so let me edit things. I’ll set a relationship between dbo.players.playerID and dbo.batting.playerID. This gives me the following in my options file.

Before I run my subsetter, here are the row counts by table.

I’ll re-run this subset command, which includes my option file at the end.
```
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:\Documents\git\TDM-Demos\rgsubset-options-bb.json
```
When I do that, I know see these row counts. Note I now have batting rows.

My player query won’t work, so I still need to declare another relationship with the dbo.teams table. That is shown below:

I can re-run the same command above, and then I see this set of rowcounts (original on left, subset on right).

There is teams data, and if I re-run my queries from the top, I can see stats now.

Now I have a dataset that I can perform development work with in terms of players, teams, and batting.

I can also add more relationships as needed, for example, I’ll add this section to include pitching, batting post, and fielding. Here’s my complete options file:
```
{
  "jsonSchemaVersion": 1,
  "startingTables": [
    { 
      "table":
      {
        "schema": "dbo",
        "name": "players"
      },
      "filterClause": "birthState = 'CA'"
    }
  ],
  "manualRelationships": [
    {
      "sourceTable": 
        { 
          "schema": "dbo", 
          "name": "players"
        },
      "sourceColumns": [ "playerID" ],
      "targetTable": 
        { 
          "schema": "dbo", 
          "name": "batting" 
        },
      "targetColumns": [ "playerID" ]
     },
     {
        "sourceTable": 
          { 
            "schema": "dbo",
 
            "name": "batting"
          },
        "sourceColumns": [ "teamID", "yearID", "lgID" ],
        "targetTable": 
          { 
            "schema": "dbo", 
            "name": "teams" 
          },
        "targetColumns": [ "teamID", "yearID", "lgID" ]
       },
       {
          "sourceTable": 
            { 
              "schema": "dbo", 
              "name": "players"
            },
          "sourceColumns": [ "playerID" ],
          "targetTable": 
            { 
              "schema": "dbo", 
              "name": "battingpost" 
            },
          "targetColumns": [ "playerID"]
         },
         {
            "sourceTable": 
              { 
                "schema": "dbo", 
                "name": "players"
              },
            "sourceColumns": [ "playerID" ],
            "targetTable": 
              { 
                "schema": "dbo", 
                "name": "pitching" 
              },
            "targetColumns": [ "playerID"]
           },
           {
              "sourceTable": 
                { 
                  "schema": "dbo", 
                  "name": "players"
                },
              "sourceColumns": [ "playerID" ],
              "targetTable": 
                { 
                  "schema": "dbo",
 
                  "name": "fielding"
                },
              "targetColumns": [ "playerID"]
             }
  ]
}
```
After re-running the subsetter, I have these row counts. Note there are rows in all the tables defined in the options file.

I can keep adding in more tables as needed to ensure the subsetter can walk down the data relationships I need in my database to produce a useable dev/test dataset that’s smaller than production.

TDM can help your devs build better software and with the subsetter, this can create lots of agility to ensure the data you need to accurately build this software is available.

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:
February 12, 2025
Friday Flyway Tips: Autopilot in 10 minutes
The Solutions Engineers at Redgate recently released an Introduction to Redgate Flyway Autopilot course on our Redgate University. They’ve been working on this for quite some time to help people get started with Flyway in their own environment. It’s gotten smooth and slick, so I’m going to set this up in 10 minutes in this post and video, but with a twist. I’m using my schema to show you how easy this is.

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.

Getting Started

The course walks you through a few things. These include:
- Getting Git
- Installing Flyway Desktop
- Having an Azure DevOps or GitHub account
- Having a SQL Server or PostgreSQL server (I’ll use SQL Server)
You will also get a Redgate Token and set up a local runner. I won’t detail those steps here, but I will have them in another post. The video will also skip those steps.

Creating a Repository

I’m working in GitHub, but you can do this in Azure DevOps. Others work, but those aren’t in the course. The main thing to do is go to the official Redgate repo at: https://github.com/red-gate/Flyway-AutoPilot-FastTrack

This brings you to this site:

From there, don’t clone or fork, but use as a template. This is in the upper right corner.

When you click this, you get the Create a new repository page, that looks like this. If you’re familiar with GitHub, this looks like any other repo. Give it a name, which must be unique in your org. I added FWAutopilot as I already have an “Autopilot” repo that is public.

You can make this public or private, but just be aware of this from the standpoint of your org, especially if you add internal schemas. You can also set a description.

Once this is created, you’ll see the repo in your org. Here’s my Autopilot repo:

This is a copy of the template all set up. Now, on to Flyway Desktop.

Creating a Project

In Flyway Desktop, I’ll click the drop down by Open Project and select Open from Version Control.

Here I’ll paste in the URL of my repo. I also check that the folder for the local clone is valid. In my case, I tend to put things in Documents/Git, but you might have your own standard.

Once this clones down, you can see a repo in the path above that looks like the online repo. Flyway Desktop will also refresh the schema, which should give you this error.

This is because the databases don’t exist. As you can see, I’ve filtered to databases with “auto” in the name and I have nothing.

If I use the file | open in SSMS, I can go into the repo and into the Scripts folder, where I see this:

I want to open the CreateAutoPilotDatabases.sql script, which looks like what you see below. This creates 5 databases and adds schema objects to one of them. The goal is for Autopilot to use Database DevOps and Flyway to migrate these changes to other databases.

Run this, and I see different databases. I’ve refreshed things, and you can see Prod has nothing but Dev has objects.

Now that I have a db, let’s refresh Flyway Desktop. Now I see no changes.

Note: If you aren’t doing this on your localhost instance, then you can edit the connections to the dev database (and other databases).

Adding Our Own Schema

Don’t start deleting schema objects yet, but you can add your own. I’ll do that. I have a script that contains a schema for baseball data. The beginning is shown below, but I’ll run this in my AutopilotDev database.

After I do this, I’ll refresh Flyway Desktop again and I see my tables. This is a partial list as the full list scrolls off the screen.

I’ll select all these from the checkbox at the top next to Object Name and then click “Save to project” in the upper right. This writes the CREATE scripts for all objects to the schema model, as you can see below in the update message.

The next step (shown at the bottom) is to generate a migration script. I’ll click that.

I get the screen below, which shows me all the changes that have been made to objects. I can select one or more of these to put into a migration script (deployment script). If I don’t select them all, then I will see those I haven’t selected re-appear here and I can add them to a different script. To keep this simple, I’ve selected them all.

When I click “Generate script” in the upper right, Flyway will create a script containing all the objects I’ve selected with the appropriate create/alter/drop code inside. Here is my one large script. You can see the start of the script below. If we scrolled, we’d see the CREATE for all the tables in here.

This project is configured to automatically generate an undo script, so below the above part, there is the undo script. Again, this is just the beginning of the script. However, you can see before we drop tables, we need to remove constraints.

Once I’m happy, I can click save and this is written as a migration script (and an undo script).

I can click Verify, which essentially runs these scripts against my shadow database, but I don’t do that if I haven’t altered the scripts. You can if you want.

Now that we’ve made some changes, let’s commit those. On the right side of Flyway Desktop is the VCS blade. You can see I have 28 changes in my repo.

If I click the “28”, this opens to the commit tab. I can also click the arrow at the top and select the commit tab. In here, I see my changes and I can include all of them or some of them and write a commit message.

I’ve selected them all and written a message, so I’ll click the drop down by commit and select the combined Commit and Push.

If I check my repo, I see this commit included. You can see this altered the schema-model and migrations folders.

Now we need to keep this Database DevOps flow going and deploy our code.

Setting Up Runners

If I check the Actions tab in my repo, I see there are two workflows configured. They are the same, but one works for Windows and one for Linux. I don’t have any runs yet and I haven’t configured a runner.

If I go to Settings in my repo and the Actions | Runners area, I’ll see this. The runners are the agents that execute your code. In this case, I need to setup a new one. I’ll detail that in another post.

Once I have a runner set up, I should see something like this:

Adding Secrets

Flyway is a licensed product, so I need to tell the runner that it is licensed to use the product. If you don’t have a license, this system can get you a 28-day trial, but if you have one, you can just use that.

If you go to the token section of the Redgate portal, you should see something like this:

If you click New Token, you get a new token.

Note: I’ve deleted this token, so this code doesn’t work.

Don’t close this, but open a new browser tab for your repo. Go to the Secrets | Actions section under Settings. You should see this. Click New repository secret.

The documentation notes you need to add two secrets: FLYWAY_TOKEN and FLYWAY_EMAIL. These are essentially secret variables picked up by the automation. When I click new, I add the email like this.

I added the token in the same way, pasting in the token from the portal. When I finished, I see two secrets.

Run the Automation

Check your production database (and the test one). There should be no objects, which is what we saw above.

Now, go to the Actions section of your repo. Click the Windows workflow on the left (or Linux if you used that).

Now on the right, click Run workflow, and then Run again in the pop up.

In a minute, your web page should show this running with a yellow circle before the name.

Your CLI window should look like this as well, with a job running.

If you click then name of the run on the web page, you should then see the three tasks. Here my build completed before I could get the screenshot, but yours likely has the yellow on the build database.

If I click any of the tasks, I’ll see the logging as they run. In this shot below, I’ve clicked the running prod deploy, as that was running when I was ready for the screen shot.

The output scrolls along and can be hart to follow, but after any of these are complete, you can click on them and see the task outline. Each of these items below can be expanded by clicking on the angle bracket. You can see I’ve expanded the Migrate Test DB task.

However, most of the time we assume we have a repeatable, reliable execution of our migration, so we don’t care. The proof is in checking the databases.

Here is my refreshed AutoPilotTest database.

and here is the AutopilotProd database.

I moved code from dev –> VCS –> test –> prod without executing it anywhere past Dev. This is the way changes should be made to test them before they hit prod.

We should also have feature branches, PRs, and more, but that’s beyond the 10 minutes to get started. From here, I could easily make other changes in dev and get them deployed by clicking a button in GitHub.

Summary

This process took me ten minutes. To be fair, I’d tested it a few times, but in knowing what things are needed in the docs, it took me ten minutes, which I show in a video below.

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. It works for SQL Server, Oracle, PostgreSQL and nearly 50 other platforms.

Video Walkthrough

I’ve got a video of me doing this in 10 minutes.
February 7, 2025
Picking a Starting Table in Test Data Manager
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:\Documents\git\TDM-Demos\rgsubset-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:
February 5, 2025