Friday Flyway Tips–Capture the Filegroup for Tables

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 FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAshadow_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 FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAshadow_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: