AzureSQLMaintenance tries to rebuild indexes online in table with FILESTREAM columns

[GertArnold]

On a SQL17 instance, Developer Edition (64-bit), we have a table with FILESTREAM columns. When AzureSQLMaintenance (current version) decides that its PK be rebuilt we get:

ALTER INDEX [PK_Medium] ON [dbo].[Medium] REBUILD WITH(ONLINE=ON,MAXDOP=1); FAILED : 2725An online operation cannot be performed for index 'PK_Medium' because the index contains column 'ReducedImage' of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

It seems that determining OnlineOpIsNotSupported doesn't take FILESTREAM into account. I think that the line

where t.name in ('text','ntext','image')

should be

where t.name in ('text','ntext','image') OR c.is_filestream = 1

Our table:

CREATE TABLE [dbo].[Medium](
    [MediumId] [int] IDENTITY(1,1) NOT NULL,
    [MediumType] [nvarchar](50) NOT NULL,
    [MediumContent] [varbinary](max) FILESTREAM  NULL,
    [ReducedImage] [varbinary](max) FILESTREAM  NOT NULL,
    [InsertDateTime] [datetime2](7) NOT NULL,
    [UpdateDateTime] [datetime2](7) NOT NULL,
    [RowGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FileName] [nvarchar](260) NULL,
    [CreateTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Medium] PRIMARY KEY CLUSTERED 
(
    [MediumId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] FILESTREAM_ON [ebrida_filestream],
 CONSTRAINT [UQ_Medium_RowGuid] UNIQUE NONCLUSTERED 
(
    [RowGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [ebrida_filestream]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_MediumContent]  DEFAULT (0x) FOR [MediumContent]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_ReducedImage]  DEFAULT (0x) FOR [ReducedImage]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_InsertDateTime]  DEFAULT (sysutcdatetime()) FOR [InsertDateTime]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_UpdateDateTime]  DEFAULT (sysutcdatetime()) FOR [UpdateDateTime]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_RowGuid]  DEFAULT (newid()) FOR [RowGuid]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_CreateTime]  DEFAULT (sysutcdatetime()) FOR [CreateTime]
GO

Of course, the stored procedure is named Azure SQLMaintenance for a reason, and there's no filestream on Azure. Yet, we use this procedure in on-premise SQL instances with great success, so I guess this modification could be helpful to others.