Why is my deployment of an on-prem database to Azure SQL failing?

Question

I'm trying to deploy a database to Azure SQL and I'm getting the error below. The filegroup 'SECONDARY' does exist and it contains all of the indexes, so why am I getting this error?

Could not import package.

Warning SQL0: A project which specifies SQL Server 2022 or Azure SQL Database Managed Instance as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 30084, Level 16, State 1, Line 1 The full-text index cannot be created because filegroup 'SECONDARY' does not exist or the filegroup name is incorrectly specified. Specify a valid filegroup name.

Error SQL72045: Script execution error. The executed script:

CREATE FULLTEXT INDEX ON [dbo].[OLS_PRODUCT_SEARCH]

([SEARCH_TAGS] LANGUAGE 1033)

KEY INDEX [PK_OLS_PRODUCT_SEARCH]

ON ([FTC_OLS_PRODUCT_SEARCH], FILEGROUP [SECONDARY]);

Answer 1

Hi @Thomas Reasoner,

Thanks for the question and using MS Q&A platform.

I can see some warnings and errors while deploying an on-prem database to Azure.

Here are some steps that might solve the issue.

When migrating from on-premises SQL Server to Azure SQL Database, you may encounter errors when trying to create a full-text index, particularly due to unsupported filegroup references. Here’s how to correctly structure your commands:

• Before making a full-text index, make sure there’s a full-text catalog. If there isn’t one, you can create it with this command: CREATE FULLTEXT CATALOG [FTC_OLS_PRODUCT_SEARCH] AS DEFAULT;
• When writing your CREATE FULLTEXT INDEX statement, don’t include any filegroup references. Use this syntax instead CREATE FULLTEXT INDEX ON [dbo].[OLS_PRODUCT_SEARCH] ([SEARCH_TAGS] LANGUAGE 1033) KEY INDEX [PK_OLS_PRODUCT_SEARCH];
• Azure SQL Database does not support multiple filegroups, so any references to a filegroup other than the default (primary) need to be removed from your scripts.

Execute the above commands in SQL Server Management Studio (SSMS) connected to your Azure SQL Database instance to verify that they work without errors .

Make sure that your user account has the necessary permissions to create full-text indexes.

By following these steps, deployment of an on-prem database to Azure SQL will be done successfully.

I hope this information helps, please do let us know if you have any Queries.