Tag: FWTips

  • Friday Flyway Tips–Linking Commits to an Azure DevOps Work Item

    Recently I was doing a demo and a customer asked how I had linked my commit in Azure DevOps to the work item that existed. It’s easy, and tldr; it’s with the commit comment. This post shows how that works.

    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.

    Creating a Work Item

    In Azure DevOps, there is a board menu. Under here, one of the subitems is Work Items. You can see these below:

    2023-12-13 17_52_28-Work items - Boards

    I tend to work from the “Boards” item, which looks like this:

    2023-12-13 17_52_22-Westwind Team Features Board - Boards

    Let’s assume we have a new item, so I’ll click the “New Item” at the top of the left column. This gives me an edit box, which I’ll type in a name.

    2023-12-13 17_54_25-Westwind Team Features Board - Boards

    When I hit enter, this gives me a new work item, which you see below, as 250 create products entitty.

    2023-12-13 17_55_19-Westwind Team Features Board - Boards

    Since I don’t want to leave a spelling error, I’ll edit the title to look better.  Now I have a work item.

    Linking A Commit in FWD

    In SSMS, I’ll create the products table. Once I do this, I’ll go to Flyway Desktop and refresh the schema model. When I do this, I see my change. I’ll select this and save to the file system.

    2023-12-13 18_01_48-Flyway Desktop

    Once this is done, I’ll go to the Version Control tab, and I again see my file changed. Here my file is selected, and I’ll enter a commit message, but I’ll include the work item number after a #. This creates a link.

    2023-12-13 18_02_45-Flyway Desktop

    I now click commit and push. In Azure DevOps, in the Repos –> Commit item, I see my commit.

    2023-12-13 18_04_15-Commit 1421f994_ #250 create products table - Repos
    Note the details item under my cursor. If I click that, I see commit details and near the bottom, a link to the work item.

    2023-12-13 18_04_21-Commit 1421f994_ #250 create products table - Repos

    If I click through to the work item, or I access the work item from Boards, I will see my commit linked on the far right of the work item.

    2023-12-13 18_04_32-Westwind Team Features Board - Boards

    This is very handy to have your actual code linked to the work that it relates to. If I have PRs or multiple commits, as long as you use the #nnn, where nnn is the work item, you’ll have everything linked together.

    This is less a Flyway Desktop feature than an Azure DevOps one, but you can access this from Flyway Desktop by writing good commit messages with work item numbers.

    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:

     

  • 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).

    2023-12-05 14_07_37-Flyway Desktop

    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.

    2023-12-05 14_10_13-Flyway Desktop

    I’ll run migrate, which works.

    2023-12-05 14_12_58-Flyway Desktop

    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.

    2023-12-05 14_13_14-Flyway Desktop

    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.

    2023-12-05 14_14_55-Flyway Desktop

    Getting Pending Migrations Back

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

    2023-12-05 14_15_50-Flyway Desktop

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

    2023-12-05 14_16_00-Flyway Desktop

    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:

     

  • 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).

    2023-12-05 15_07_27-Flyway Desktop

    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.

    2023-12-05 15_08_15-Flyway Desktop

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

    2023-12-05 15_08_58-Flyway Desktop

    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.

    2023-12-05 15_09_06-Flyway Desktop

    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.

    2023-12-05 15_09_15-Flyway Desktop

    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

    2023-12-05 15_09_38-Flyway Desktop

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

    2023-12-05 15_12_16-Flyway Desktop

    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:

  • Friday Flyway Tips–Comparison Options

    Recently a customer asked how they could get index changes to be captured in Flyway Desktop. In their case, they wanted a different fill factor, but I decided to investigate a bit more how things work.

    This post looks at how to control the comparison options in Flyway Desktop (FWD).

    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.

    The Setup

    I’ve got a table and an index, which I created with this script.

    CREATE TABLE [dbo].[Customer]
    (
    [CustomerID] [int] NULL,
    [CustomerName] [varchar] (75) NULL,
    [PrimaryContact] [int] NULL,
    [PrimaryAddress] [int] NULL,
    [PurchaseLimit] [numeric] (10, 2) NULL,
    [Status] [tinyint] NULL
    )
    GO
    CREATE NONCLUSTERED INDEX [nci_customer_custname] ON [dbo].[Customer] ([CustomerName], [Status])
    GO

    I saved this in Flyway Desktop, which we can see here in the filesystem:

    2023-11-28 09_48_55-Window

    and here in VS Code.

    2023-11-28 09_52_32-Window

    If I refresh the schema model tab in FWD, there are no changes.

    Making Index Changes

    I’m going to alter this index. Specifically, I’m changing the pad index, fill factor, and statistics options. Here’s the script I’ll run.

    ALTER INDEX [nci_customer_custname] ON [dbo].[Customer] 
    REBUILD PARTITION = ALL 
    WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF, 
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    

    Once this runs, I’ll refresh FWD and I see this. Note that I see an index change, but only one option is captured: STATISTICS_NORECOMPUTE.

    2023-11-28 09_54_21-Window

    What’s happening is that FWD is using the SQL Compare engine and the default options set in the engine. Among these are to ignore fill factor and pad index. However, I can change this.

    Changing Configuration

    In the past, I would need to edit a config file to make this change, but the team has enhanced FWD to add new options. In this case, notice the button near the top of the Schema model tab: Static data & comparisons. Not a great name, but it’s there:

    2023-11-28 09_56_31-Window

    Once I click that, I get a new dialog. This starts with static data, but I’ll click the second tab, which is Configure comparisons. This shows all the options available in the Compare engine

    2023-11-28 09_56_37-Window

    Rather than scroll, I’ll type in the search box, and I see fill gets me the “Ignore fill factor and index padding” option.

    2023-11-28 09_56_42-Window

    I’ll uncheck this and click OK.

    Once I do that, I’ll refresh the comparison, and now I see my options.

    2023-11-28 09_58_46-Window

    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: