Invoke-DatabaseBuild with Table with a Full Text Index

AlbertWong

Hello,

I'm trying to figure out why I cannot deploy my database repo to my target database. It seems to be related to a filegroup for a full text index on one of the tables.

Invoke-DatabaseBuild, SQL Change Automation 3.3.19197.9413, Copyright © Red Gate Software Ltd 2014-2019
Validating SQL Source Control project 'C:\Source\svn\CAHCO_DB_PPD_ESB\lods\dev_3.0.0\src\PPD_ESB'.
Exception while validating the database project at C:\Source\svn\CAHCO_DB_PPD_ESB\lods\dev_3.0.0\src. Exception was The full-text index cannot be created because filegroup 'ftfg_ft' does not exist or the filegroup name is incorrectly specified. Specify a valid filegroup name.
Local parameters [connectionString = Data Source={redacted} Catalog=dlmautomation_14501d96-5ec6-4784-952c-70b6b73d496e;Integrated Security=True;Application Name="SQL Change Automation"]

The filegroup 'ftfg_ft' does exist though. So, I don't understand what the cmdlet is complaining about.

In the table definition, there's these statements:

CREATE FULLTEXT INDEX ON [dbo].[Address] KEY INDEX [PK_AddressID] ON ([ft], FILEGROUP [ftfg_ft])
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([Address] LANGUAGE 1033)
GO

If I run those myself, they succeed. The table definition .sql file was created by SQL Source Control.

Kendra_Little

Hi Albert,

Quick check-- are you saying that you have already created a pre-deployment script which configures the filegroup automatically for a new database? I'm not completely sure where you are saying that the filegroup 'ftg_ft' exists.

Some more details on how this works:

• The error message you shared (thanks for including that!) mentions Catalog=dlmautomation_14501d96-5ec6-4784-952c-70b6b73d496e;
• That means that you have the build configured without a hardcoded database name. During the build process, it creates a database on the instance where you have the build specified, uses it for the build, and then cleans up after.
• That new database will be created based on the model database of the SQL Server instance where you are building -- so if model only has a primary filegroup, then the build database will only have a primary filegroup
• If you would like this dynamically created database (and other databases you deploy to later in your pipeline) to have additional filegroups, you can check for existence of the filegroup and configure it in a pre-deployment script -- this page has sample code.

Hope this helps!
Kendra

AlbertWong

Oh, I did not create a pre-deployment script to create the filegroup. Let me try that.