Voice of the DBA

Author: way0utwest

  • Friday Flyway Tips: More Config Options

    Working with various Flyway configuration options used to be a pain since they were either CLI parameters or in a text files. We’ve made editing these 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.

    All the Options

    Maybe not all, but most and certainly more.

    In the Schema Model tab, there is a button that says “Static data & comparisons”. I don’t love the UX, but there’s a lot of stuff to show here, and this is really relevant here, which is where we start using these options: when we get the object definition.

    2023-08-10 15_41_20-Flyway Desktop

    If you click this, you’ll see a dialog pop up, with any tables where you are tracking static (or reference/lookup) data. There are two other tabs: comparison options and data options.

    2023-08-10 15_41_28-Flyway Desktop

    Clicking comparison options shows you the options for schema comparison. Essentially the options for SQL Compare (for SQL Server), Schema Compare for Oracle (for Oracle), or Redgate Compare (everything else). You can toggle these on or off by clicking checkboxes.

    2023-08-10 15_41_36-Flyway Desktop

    Likewise, there are the static data comparison options, equivalent to project settings in Data Compare.

    2023-08-10 15_41_48-Flyway Desktop

    There are still a lot of options, and these can be confusing. Knowing what is set and choosing those isn’t simple, but it is easier than trying to edit these in a config file.

    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:

  • Removing Weak Security from SQL Server

    I was checking some arguments in the RESTORE command for SQL Server and saw that the MEDIAPASSWORD option was deprecated and marked as being removed at some point. That made sense, and I assumed that PASSWORD was the option to be used moving forward. However, that option is also marked as deprecated.

    What should we do?

    Well, we need to better secure our backup files for sure. Disk encryption and limits on AD/directory permissions ought to be set. Of course, we need to use care when handling or moving these files, especially when they cross the secure boundary in production systems to other dev/test/etc. networks.

    The security section of the document explains the reasoning here. The protection provided is weak and isn’t intended to protect data. You can still read data in the backup file. This is mainly to prevent an incorrect restore when using tools, meaning the human picking the wrong file. This isn’t to protect your data.

    I suspect most people dealing with SQL Server backups that use either of these options don’t know this. They think the password secures their data. I know because I’ve seen people use this to send a backup file through email or file transfer to another party. However, if you’ve ever opened a backup file in XVI32 or another editor, you will see that your data is in plain text. If you’ve never done that, give it a try today and search for strings that you know are stored in the database.

    Some security is better than no security and layers of security that build on each other are useful. However, depending on weak security isn’t good. It leads people to count on something that doesn’t work and ignore more serious issues.

    I’m glad that Microsoft is (supposedly) removing these options. I understand that backwards compatibility and preventing existing scripts from failing are important. At the same time, we need to move forward. I’d like these password items to become a no-op, and not cause errors, but I would hope that their use in scripts would also generate a message to the user that these options don’t work and need to remove removed. Perhaps with a direct message and a note in the error log, we’d start to see people embracing other security practices that will provide more protection.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • A New Word: mahpiohanzia

    mahpiohanzia – n. the frustration of being unable to fly, unable to stretch out your arms and vault into the air, having finally shrugged off the burden of your own weight, which you’ve been carrying your entire life without a second thought.

    Isn’t everyone other than Michael Jordan and a few others feeling this? I know the kids I coach in volleyball dream of flying, not for long, but always more than they can do.

    I’ve felt this as well when I was in my 20s and trying to dunk a basketball. I did it a couple times, but then I struggled as I got even slightly older (and heavier). AS I moved to volleyball, I used to wish this. Now I worry about landing on these 50+ year old knees, so I don’t think about mahpiohanzia.

    From the Dictionary of Obscure Sorrows

  • The Redgate DevOps Roadshow–Houston

    I’m heading to Houston today for the Redgate Database DevOps in a Day workshops. This is the first of many on the US tour. I’ll be at 5 of them, but there will be another 5 where Ryan, Grant, or our Solutions Engineers will run the show.

    If you’ve registered, please say hi, introduce yourself, and ask lots of questions. Hopefully you’ll enjoy the day.

    Just a quick trip for me, coming back Friday, which is good. I head to the Chicago DevOps in a Day workshop next week.