Error when replicating Full Text indexes from SQL Server to an old Azure SQL database

Question

When trying to deliver a snapshot for the transactional replication from SQL Server database to an old Azure SQL database (the database created long before Microsoft introduced Full Text support in Azure SQL) I get the following error:

Command attempted:
if databasepropertyex(db_name(),'IsFullTextEnabled') = 0 and isnull(is_member('db_owner'),0) = 1
    exec sp_fulltext_database 'enable'

(Transaction sequence number: xxxxxx, Command ID: xxxxx)
    
Error messages:
DBCC command 'CALLFULLTEXT' is not supported in this version of SQL Server. (Source: MSSQLServer, Error number: 40518)

I noticed that for very old Azure SQL databases the IsFullTextEnabled flag is set always to 0 despite having the Full Text capabilities working just fine. This is in contrast to newly created Azure SQL databases, which always get mentioned flag set to 1 by default.

Looks like a bug in the replication logic present in SQL Server (I checked 2019 and 2022 versions so far), because that flag is deprecated in Azure SQL and should never be evaluated by new logic, yet SQL Server replication still tries to evaluate it. Does anyone know how to overcome this issue and make the replication from SQL Server to Azure SQL work together with full text indexes? For now it looks like the only viable solutions, I can think of, are:

1. Do not replicate full text indexes at all to such old Azure databases.
2. Create a brand new database and replicate to it leaving the old one no longer used.

However both approaches are not perfect and have some disadvantages. Can I somehow change the IsFullTextEnabled flag to 1 for such old Azure SQL databases or force the replication logic to not evaluate it at all?

Answer 1

There is an undocumented option to the snapshot agent that can force the replication logic to not try to enable Full Text support on the target database. It is called:

-NoAutoFullTextIndexEnablingAtSubscriber 1

After adding it at the end to the command of the "Run agent" step in the snapshot agent definition the replication logic will no longer evaluate the IsFullTextEnabled flag on the target database assuming the Full Text functionality is present and enabled, which should completely resolve the mentioned issue.

WARNING: Because the mentioned option is an undocumented one, it is not officially supported and may disappear at any time in the future or just suddenly stop working as expected. Then I suggest to treat this answer as a work around rather than a final/stable resolution.