Voice of the DBA

Tag: Redgate

Friday Flyway Tips–Above Target Migrations

Recently I was working with Flyway Desktop (FWD) and practicing deploying certain migrations and not others. I don’t recommend this, but a customer was doing this and I wanted to reproduce some things they were trying to help them.

In doing this I discovered a bunch of scripts marked as “Above Target” in the GUI, but not in the database. I learned a few things from our support team.

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.

Setting a Target

I’ve got a FWD project here, and you can see the migrations below. In this case, I’ve selected a target of my QA machine and we can see that I have migrations applied up to 3.1 and there are pending migrations from 4-8 (ignore the undo).

Now, I want to migrate QA, but only to version 5. When I do this, from “Advanced Settings” on the right, I’ll see the target is listed as a parameter, and if I’ve highlighted the parameter on in the script.

I’ll run migrate, which works.

When I close this, I now see a different view. I see success next to migrations 4 and 5, but I also now see “above target” listed for the other migrations.

That’s not great, for this reason. If I check the “only show pending migrations”, I lose all my migrations. In large, complex projects, this isn’t good.

Getting Pending Migrations Back

The easy solution to this is to remove the target. If I click the “x” on the target parameters…

And then click Refresh at the top, I now see my migrations as pending.

Seems obvious, but I’d forgotten about the target as I was focused on testing the code I’d released and then wanted to release other code. Peter in support educated me on what I was missing.

Try it 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.

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:

December 22, 2023
Creating a Scripts Folder in SQL Compare

While I was at a conference recently, someone asked me about the Scripts Folder feature in SQL Compare and how to set that up. This post just looks at how to get SQL Compare to set up a Scripts folder on a machine.

This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

Getting Started

I’ve got a D: drive on my machine that looks like this.

I’m going to create a folder, called “Compare Script Folders”, which you can see is empty. This is where I’ll store the scripts for various databases.

I also have a couple databases I use to test things on a local instance. You can see below my “compare1” database has a few things in it.

Now, I want to get a text copy of my schema from SQL Compare that I can use to check for changes, so let’s do that.

Creating a Scripts Folder

In order to create a scripts folder, I need to run SQL Compare. Once I do that, I see a screen like that shown below.

By default, SQL Compare looks to compare two databases. However, I can change the source. If I click the drop down next to Source, I see all of the options I have available. One of these is Scripts folder, which I’ll select.

Once I do this, my dialog changes. Now, I have the ability to compare a Scripts folder to a database. How do I get the Scripts folder? I click the Create link shown below.

I missed this a few times, so that’s part of why I wrote this post. Once I click that, I get a new dialog. This is the one that let’s me choose the way I want to create the folder.

Just like the comparison, I have a number of choices for the source. I can use various items, but in my case, I’ll choose database.

Once I do this, I enter the credentials to access the database. Note that I’ve clicked “Trust”, which is required for all modern versions of SQL Server. I have also selected the new folder I created.

Once I click Create, the engine will start to script out my database into separate files. This is the same process used when running a comparison, but in this case the results are just output to files rather than held in memory to compare with another set of objects.

Once the comparison completes, I see a new folder created below the folder that was in my dialog.

Inside this folder are all my objects, separated into different folders, which is the SQL Compare structure. This is the same structure used in SQL Source Control and Flyway.

Now I can use this as the basis for a source or target against another source or target in SQL Compare.

If you want to do this from the menu, there’s also an item in the File menu to get the Create Scripts folder dialog.

SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.

December 20, 2023
Friday Flyway Tips–Deploying Migrations with a Target

Recently I was working with Flyway Desktop (FWD) and helping a customer work on deploying part of their work. They weren’t sure how easy this could be, but this post follows what I showed them.

Using the ability to run Flyway commands in FWD, we can deploy some migrations and not others. This post shows how to configure this.

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.

Picking Migrations

I’ve got a FWD project here, and you can see the migrations below. In this case, I’ve selected a target of my QA machine and we can see that I have migrations applied up to 5 and there are pending migrations from 6-8 (ignore the undo).

If I want to apply migration 6, but not 8, I can do that. First, I’ll click the Advanced settings on the right side. When I do that, I see text with a “add parameters” button.

If I click the Add parameters button, I get a drop down that is searchable.

I can start typing “ta” in here and you see matching items. “Target” is the last one and this is the parameter that you want.

The value of the target is the last migration you want to run. In this case, I can pick 6 and it will run only migration 6. If I pick 7, it will run 6 and 7.

Once I do this, I can click back (or add more parameters) and on the main screen I see that target is in blue, as a parameter added. In the command text box, I’ve highlighted this command as added to the CLI.

Note: This text is what you could run in a CI system or at a cmd/shell .prompt

When I click migrate, the command is run and I get output about which migrations ran.

If I close this, then I see 6 is successfully applied (after unchecking only show pending) and 7 and 8 are above target. In another post, I’ll explain those.

Try Flyway Desktop 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.

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:

December 15, 2023
Clearing Intellisense in SQL Prompt

I got this question from an account rep: if a customer is using SQL Prompt and a snippet, for example AT, that was also used as an alias, is there a way to avoid it triggering the snippet or do they just avoid hitting the tab key?

It’s a good question. Let’s see how to deal with this.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

The Scenario

Say I have this code:

Notice I have an alias that is the same as a snippet, in this case AT. Seeing this pop up is annoying, but there are ways to avoid getting concerned about this.

First, the Esc key will get rid of the intellisense popup. Pressing escape at this point brings me back to this:

My other option is to type a ., essentially keep typing. If I do that, I get this:

The same thing if I had a longer alias. Here I’ve added 2 characters, but if I type the 3rd, the snippet goes away and I get the table. I could hit tab here to fill this in or type the s.

The other thing to do is raise the time for intellisense if it’s popping up too quickly and annoying you. I don’t know the default (nor do I want to reset lots of stuff), but here I’d set it to 100ms, which is a nice delay for me. I find 500 too long. Of course, you can CTRL+space to pop it open.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

I made a video of getting rid of intellisense. You can also see all my SQL Prompt Tips.

December 13, 2023