Voice of the DBA

Tag: Redgate

Friday Flyway Tips: Searching a Migration

This was actually a cool tip I saw internally from one of the product managers, when trying to find specific text in a migration.

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.

A Large Project

I’ve got a project in Flyway Desktop that I add to regularly. It doesn’t do anything amazing, but I keep slowly adding objects, with the idea that I want to build up a large project for showcasing different things to customers. You can look at the project here if you are interested.

In my list of migrations, I’ve got a number of migrations, 21 to this point. It is a little hard to read names here, but I’ve named each script to make searching easier. I’ve included a create or alter, a type, and an object name. This helps me use the search box at the top of FWD, which searches migration names.

Most of these are relatively small scripts, one or two objects. A few are a little larger. If I select a particular one (21 as shown below), the code scrolls off the screen. Even if I expand the code listing to take up the entire screen, I can’t see all the code.

More importantly, it can be hard to visually find something in the code. If you’ve ever edited a stored procedure of 100 or more lines, you know what I mean.

One of our PMs pointed out recently that you can use the standard browser CTRL+F if you’ve clicked into the script. I’ve done this below and the search widget pops up at the top of the code. I’ve entered part of an object name here, prod, and you can see this shows 6 matches.

The first one is highlighted in a pale yellow color in the code.

If I click the down (or up) arrows in the search widget, the code jumps around, as it would in a browser. You can see below I’ve jumped down to the 4th occurrence, with the 2nd and 3rd highlighted in an orange(ish) color, while the 4th also has the yellow background bar.

Many of us would perform work on this code in an IDE (SSMS, ADS, etc.), but while managing a project or perhaps evaluating something else, we might want to quickly find something in code in FWD. Perhaps reviewing a change I’m about to commit. USing CTRL+F might be a handy feature.

I do wish the search was more comprehensive at the top, and could dig through all the files in the project, but at least I can search within a file here. Having a good naming convention for migrations also helps me to find the high level purpose for a migration in the list.

Flyway Enterprise

Try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

If you use Flyway Community, download Flyway Desktop and get a GUI for your migration scripts.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

October 11, 2024
Monday Monitor Tip: Compliance Checking for Old Versions

A customer recently wanted to know if any of their instances were too old and out of support. This was for a compliance purpose, and they had the need to show a report to management of when instances were out of compliance with policies.

This post shows how you can do this in Redgate Monitor.

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

Compliance Templates

This is an Enterprise feature, but it allows the administrators of Redgate Monitor to define templates for how the various servers and databases ought to be configured. Deviance from standards, or compliance with standards is often something auditors care about you documenting.

This is very easy with Redgate Monitor. Let’s see how this works.

Under the Security tab (appears in Enterprise edition), you can see there is a Configuration Compliance item. Click this.

This brings up the Configuration Compliance screen. This starts with Servers (highlighted with a bar to denote this tab, but there are also databases and Compliance templates. We want to choose compliance templates.

This tab shows the various templates that exist already in the system. I’m looking at monitor.red-gate.com, which has three templates configured already.

If you scroll to the right, we can see that two of these are server (instance) templates and one is a database template. We can add a new one, or edit existing ones. For this post, I’ll edit the Workload Server template.

After we click Edit, we get another set of tabs for the template itself. The default is for security, but there are also performance and environment options. Pic the Environment tab.

This brings up a list of settings. Scroll down to the bottom and you will see what settings below for Product Level, Product Version, Product version number, and Edition.

In this case, the template is checking that every server has at least SQL Server 2019 Enterprise installed. If any other version were installed, this would show as non compliant. This is useful for grouping your servers by versions.

The values entered here are the ones you would get back from the ServerProperty() function.

Summary

This post shows how you can configure a compliance template to check that the versions of SQL Server you have installed meet your requirements for a version.

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.

September 30, 2024
Monday Monitor Tips–Finding CUs for My Instance

How can I quickly get a CU patch for a system that’s out of date? I’ll discuss that situation.

You might think you get to patch every instance every few months, and you may be able to. But most of us have laggards in any decent-sized estate. Someone always wants to avoid patching, or skip patching on the day you’ve scheduled every other system.

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

Tracking Versions

The Estate page in Redgate Monitor contains quite a few different views of your entire estate. This section is designed to aggregate data across all the systems you are monitoring. If I look at https://monitor.red-gate.com/Estate/Versions, I see this as a default.

This gives me an overview of what SQL Server versions I’m monitoring. As you can see, our test estate has a mix of versions, from 2008R2 through 2022 and one Managed Instance. The counts are in the pie chart, and we can see how many are up to date in the bars to the right of each version. As you can see, lots of our estate needs patching.

I can also see at a glance for each version that the latest update is and its release date. The download link is a quick way to download the latest patch from Microsoft. We maintain this list and Redgate Monitor will update it on a regular basis.

When I scroll down, I see the details of individual instances. These are grouped, though I can change that with the toggle in the upper left. For each instance, I have the name and the major version,

The current status and the latest patch are listed next to each other, with an icon and color coding that let’s me know I need to upgrade (yellow up arrow) or I’m patched (green checkmark). I once again have the latest patch, linked to the MS article as well as text that let’s me know how out of date I am. You can see these patches are a month old. Auditors are potentially OK with that.

However, for my 2012 instances, I’m way out of date. No excuse for that. We keep these firewalled and protected, and they are available here only for demonstration purposes.

To the right of this we have our support dates. If a date is past, we mark it with a triangle to let you know that you have unsupported versions. That may or may not be an issue for your organization.

Summary

This section of the Estate tab isn’t something I expect DBAs or sysadmins to check often, but I would schedule a reminder to do this quarterly. Knowing the state of our patching process is important, especially when there are security updates being released. We have had a few in the last year for SQL Server, and it is important to patch and apply those.

Seeing not only the status, but having an easy download link makes this a very handy tab that I wish I’d have had in quite a few jobs during the 90s and early 2000s. If you haven’t checked your estate tab in Redgate Monitor, you might do that today.

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.

September 16, 2024
Redgate Clone Graduating Images
I wanted my own custom image in our internal Redgate Clone cluster, so I decided to make one. This is an overview of how this works.

This is a series of posts on Redgate Clone, which is a way of quickly and easily creating ephemeral developer images of databases.

Overview

Usually a custom image comes from production, which is masked and subsetted, then available for devs. However, if you are working in greenfield development or want to experiment, you want to start somewhere. I decided to start with an empty instance and customize it.

The process I followed is this:
- create a blank container (create)
- make changes to the container
- save the changes (save)
- create an image from the container (graduate)
I’ve added the RGClone commands in parenthesis after each of these steps.

Creating a New Container

We have an internal Redgate Clone cluster that many of our developers and teams use for spinning up database environments. I connected to this and created a data container using this command.
```
rgclone create data-container –image empty-sql-current
```
This is a blank image we have that contains SQL Server 2019 on it (current was a poor choice for naming) and no databases installed. As you can see below, this created the container and then returned the endpoint in a connection string, including credentials.

When I then listed out the containers running, I could see mine below. It’s the last one, where the container name is the image with a random set of characters appended.

Once this was done, I decided to connect to this instance and then customize things. This container is just a plain SQL Server instance, and appears like that on the network, so I connected to it in SSMS.

Customizing things

Once I connected, I could see this was a standard SQL Server instance, just as if I’d installed SQL Server on a VM.

I had a script to create a database, so I ran that and created a new database and added some data. When I was satisfied with my changes, I decided to save this as a new image.

Graduating the Image

I don’t know why graduate was chosen as the verb, but it was. This means we’ll take an existing container and use it as the source for a brand new image. To do this, we follow a two step process: we save the container state and then create a new image.

The first step is saving, and we use the save verb with the container who’s state we are saving. This creates a revision of this particular moment in time of what the container looks like. I didn’t include a version, since I was learning. The default is rev.x, where x is the next numeric version.

Here’s the command and output.
```
rgclone save data-container empty-sql-current-wyzpthav
```
Now that I have a saved state, let’s use that to create a new image. I used the graduate verb here, but this didn’t work. There is an error that ran off to the side, which noted there was no revision number 1. That’s because the docs didn’t quite explain this, and I wasn’t sure.

The better command is:
```
rgclone graduate data-container empty-sql-curent-wyzpthav –revision rev.1 –name sql-2019-cu22-Northwind
```
where we give the contained and the actual revision used (or returned from the save command. In this case, rev.1 is the revision. I also gave this a better name, which reflects the actual version of SQL Server.

Once this was done, I could see my sql-2019-cu22-Northwind image in the list of data images. I could easily create a new container instance from this, which would be my starting point for more development work.

Summary

This is a basic look at how to use Redgate Clone to create custom images based on your work. Ideally, a bunch of this work would be automated, so we could get updated images of the current state of our development systems as we evolve and change our schema and data.

There are ways to automate things, often starting with a backup, but if you have developers that might want or need to customize a container and want to deploy that to others, this is a way to do that.

Redgate Clone is a part of our Test Data Manager solution, which includes the ability to also mask and subset your data.
September 9, 2024