Tag: Redgate

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:

I saw a post that Flyway Desktop has a dark mode and I had to try it out.

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.

Dark Mode

In general, I’m not a big dark mode person. I tend to use browsers in light mode, like this:

I regularly work in VSCode, which is light for me.

And of course, SSMS is light. I know lots of people have wanted a dark mode, and you can configure that, but it’s a mess. To me, I just like light mode.

However, someone posted a note internally a Redgate thanking engineers for dark mode.

Wha??? I had to try it.

It looks interesting.

I don’t know if I’ll use it, but it’s easy to enable, and I can switch anytime. In the top bar, there are a number of icons. There’s a moon for one, which you see above and below, because I’m in dark mode.

If I click the left-most one above, I see this:

Now I have a sun, and a light mode.

This works on all tabs, on the VCS blade, and it gives you the option to work with either a white or dark background.

That’s pretty cool. I don’t care so much, but I know lots of people do, so they now get the option.

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: