Voice of the DBA

Tag: Redgate

Friday Flyway Tips: Copying the Migration Number

It’s a small thing, but copying the migration number can be a pain. However, we’ve made this easier in Flyway 6.5.4.

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.

Lots of Migration Numbers.

I’ve got a lot of migrations in this project. You can see below in the image, noting the scroll bar goes up and down here.

These are easy to read numbers, but some companies use dates and times. Like this:

Those are harder to read, but more importantly, easy to mis-type. If you were using flyway migrate or undo with a specific version, you don’t want to make a mistake.

We’ve made it easy in Flyway. We added a copy button, which you can see below. This is to the right of each migration. I missed this until a dev pointed it out.

If I click this, the version is copied. I can paste it into the search, as shown here.

Or an email.

A small change, but a handy one. One way to make working with Flyway smoother and reduce mistakes. That’s a big part of DevOps, trying to reduce mistakes.

Of course, you have to click the right line.

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:

August 18, 2023
Fill Out The State of the Database Landscape Survey and Maybe Win

If you’re like me, you sometimes wonder how different other environments are from the one I work in. Well, the ones I used to work in. These days I see lots of customers environment, build PoCs, and help them solve problems, but I don’t have much of an environment for myself.

We’re taking responses for the State of the Database Landscape survey. http://rd.gt/survey Share your thoughts on platforms and what your org does. This survey is

I’m trying to get Redgate to offer some prize, but I’ll do one from my SQL Server Central budget (if I can slip it in). I’ll get a list of responses and pick 4 people from those that leave their name and email at the end. You have to fill out the survey and share the link on socials somewhere.

Spread the word, and you send me a link of where you’ve shared the contest (LinkedIn, FB, Twitter, Thread, etc.) then I’ll add an extra entry in my contest for each share. I’ll pick people who fill out the survey and have shared it and send each a USD$25 Amazon GC (or Starbucks or other major brand).

August 15, 2023
Decrypting Stored Procedures The Cumbersome Way
I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

Setup

I tested this on SQL Server 2017/2019/2022. I don’t have older instances handy, so I can’t verify that for those. However, I ran this code in databases on each instance:
```
CREATE OR ALTER PROCEDURE dbo.DecryptionTest
WITH ENCRYPTION
AS
SELECT 2 AS Two;
DECLARE @i INT = 1;
IF @i = 2
   SELECT 3 AS Two;
ELSE
   SELECT 2 AS Two;
GO
CREATE PROCEDURE [dbo].[DecryptionTest2]
WITH ENCRYPTION
AS
   SELECT 2 AS Two;
GO
```
I had shown how to detect these are encrypted in a previous post.

Decryption

I had initially sent the client these links, since I was sure they’d worked at one point. I thought that SQL Server 2000 and earlier had a different algorithm. Don’t quote me on that and there’s so much Google-noise, I can’t verify this.
- https://www.sqlservercentral.com/blogs/simple-way-to-decrypt-sql-server-stored-procedure
- https://www.sqlservercentral.com/scripts/decrypt-stored-procedures-views-and-triggers
Those links don’t work, and I can only guess that either I had used different procs or these were edited.

In any case, I found a gist here: https://gist.github.com/jstangroome/4020443

This has code that does work. I ran this on my instances, and I got similar output to this on all instance:

I didn’t clean this or try to extract the value from the XML, but for the client this worked.

It’s not the cleanest or best way, but it does decrypt the procs I’ve created.
August 14, 2023
Friday Flyway Tips: Searching Migrations

When a Flyway Desktop (FWD) project (or Flyway project) has been around for a long time, there can be a lot of migration scripts. That can be a pain for users, but there is a way to find your changes or limit what you see. This post looks at how to do this starting with Flyway Desktop 6.5.4.

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 Busy Screen

I’ve got a lot of migrations in this project. You can see below in the image, noting the scroll bar goes up and down here.

Many customers have many more migrations, which could run into the 100s. I’ve worked with large Oracle migrations in the past where there were 100s for an upgrade from v4 to v5. The project would have been 1000s in its lifetime.

Use Search

At the top of the Migrations tab, there is a new search box. This was added somewhere in Flyway Desktop 6, and I just noticed it. However, it’s very, very handy. If I start typing in there, like “New Table”, then I see just those migrations.

I can even search versions, like the specific migrations in the 3.8 release that I am wondering about.

This works by checking the version and description fields. The rest aren’t as useful, but it does mean if you come up with some standards for the description in your team, you can easily use this to find changes in your project.

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

August 11, 2023