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:
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)
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:
- Do not replicate full text indexes at all to such old Azure databases.
- 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?