Tag: Flyway

I had a customer ask about undoing changes made by developers, similar to what SQL Source Control does. I had to do a little research to show how to do this, which is the tip this week.

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.

Making Changes in Development

I’ve got a few changes I want to make. You can see these in the image below, where I’ve added a column and then a couple of tables.

I test this (hopefully) and I come to Flyway Desktop, where I can see all my changes listed in the Schema Model tab.

At this point, I realize that I’ve done something I don’t want. I don’t want the column to be named something. Maybe I misspelled it, or maybe I modeled it wrong. I can easily fix this in Flyway Desktop.

At the top, you see there is a Save to Project and Apply to Database set of radio buttons.

This is a two way comparison, with the options reversing each other. The Save item will write database changes to the file system in the repo. The Apply will read the repo and make the changes in the database.

In this case, you can see below that when I select the Apply radio button, I get the notification that my column will be deleted.

Once I click “Apply to Database” (the big blue button), these changes are made.

If I check the database, I can see my column is removed, but my tables still exist, since I didn’t check their boxes on the left.

I’ve undone a change. Now I can commit these two tables, if that’s what I need, or I can also add back a better named column and refresh this if needed.

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.

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:

One of the things I had to do recently in a demo was access the Git command line. The way I did it impressed a customer, so I put together a quick tip.

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.

Working with Git and Flyway Desktop

When working with a Flyway Desktop project, you see a screen like this. Most of the time, this works great, and as I showed in another tip, the VCS Git client is on the right side in a blade.

If you need to get to the Git repo from a shell, you need to open a shell and then navigate with CMDs to the right location. Or open the location in Explorer and type CMD. However, in the upper right corner, there is a shell icon, which is highlighted below.

If you click this, the default shell opens in the correct location. In this case, it’s not the repo root, but rather the project root.

I can then run my git commands, like “git status”, and I get relevant results for this project. As you can see below, I have 6 changes, which matches the 6 uncommitted changes in FWD.

That’s it. Quick access to the repo from the CMD shell.

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.

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:

A customer asked recently why Flyway doesn’t detect the filegroup for some changes. I showed them it does and decided to write a post on 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.

Setting Up Filegroups

I don’t see a lot of SQL Server databases using filegroups. They can be helpful, and they can create complexity, but they aren’t good or bad. Some people use them, and for various reasons. If you use them, you’ve probably run some code like this:

ALTER DATABASE [EngineRoomDemo_1_Dev] ADD FILEGROUP [indexes]
GO
ALTER DATABASE [EngineRoomDemo_1_Dev] ADD FILE ( NAME = N'index1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\shadow_index1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [indexes]
GO

Here I’ve added a new filegroup, called indexes, and a file in this group. This isn’t set as the default.

Since I’m working with Flyway Enterprise and generating scripts, I do need to also alter my shadow database, so that I can both detect changes and also verify my scripts. I’ll use this code:

ALTER DATABASE [EngineRoomDemo_1_Dev_Shadow] ADD FILEGROUP [indexes]
GO
ALTER DATABASE [EngineRoomDemo_1_Dev_Shadow] ADD FILE ( NAME = N'index1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\shadow_index1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [indexes]
GO

Now we can detect changes.

Detecting Table Changes

First, let’s actually create a new table on this filegroup. To do that, I create a table, but I add an ON clause, as shown here. After the table creation, I use ON with the name of the filegroup. This ensures this table exists on the file(s) for this filegroup.

CREATE TABLE [dbo].[newtable](
     [myid] [int] NULL,
     [mychar] [varchar](20) NULL
) ON [indexes]
GO

Once this is done, I can go to Flyway Desktop and detect the change. When my schema model refreshes, I see the change, but no filegroup.

Hmmm, what’s wrong?

By default SQL Compare ignores filegroup information. If I click the Comparison options button, I get a dialog.

In here, go to the Comparison options dialog and type “file”. Hopefully the lower option below will be fixed to say “filegroups” and also add commas soon (sorry, I’m an editor).

We ignore these by default, so I’ll uncheck the lower box.

I’ll save this and refresh my schema model. Now I see the proper script with the ON clause.

Problem solved. If I care about where objects are created, I now have the filegroup location captured as part of my code.

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.

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: