Replicating Full-Text Indexes in SQL Server 2005

Introduction

One of the new features in SQL Server 2005 is the ability to have precise control over the article properties and therefore to select exactly what is replicated along with the article. In this article I examine the ability to replicate the full-text indexes (FTIs) which may exist on a publisher's table.

Implementation

To test out this new functionality, I created a simple transactional publication and added 2 articles - a table and a stored procedure.

The table I added has a simple schema which is shown below:

The FTI exists on the column 'OrganisationName'.

Along with this table I have added another article which is a stored procedure using one of the FTI-specific TSQL commands - "CONTAINS":

Once added to the publication, enabling the option to replicate the FTI to the subscriber is shown below (it is disabled by default).

At this point we might assume we have everything necessary, as it seems a relatively straightforward setting. However, initialization results in a fatal error:

The schema script 'MyCatalog_4.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database. The functionality to disable and enable full-text search for a database is deprecated. Please change your application. (Source: MSSQLServer, Error number: 7616)

So, how to ensure that the subscriber database is enabled for FTI? We might set it up manually, or use a pre-snapshot script:

Using this command, there's no need to check the current setting using the databasepropertyex as sp_fulltext_database doesn't report an error if it is already enabled.

Once FTI is enabled at the database level we receive a different error:

Error messages:
The schema script 'TestFTISearch_3.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'ttest' because it is not full-text indexed. (Source: MSSQLServer, Error number: 7601)

This might seem odd as we have already selected to take the FTI, and checking the snapshot text files confirms that the FTI at the table-level is indeed being implemented. The problem is that the default sync_method in transactional replication is now 'concurrent'. When using concurrent replication, unique constraints that full-text indexes depend upon can not be created until the end of what is called the reconciliation phase which occurs some time after all the snapshot files have been applied (uniqueness constraints are not guaranteed to hold during the reconciliation phase). So, how to overcome this problem? We can simply update the publication to change the sync_method to be 'native' - the same as that used by default in SQL Server 2000. The disadvantage is that the tables are locked for longer periods of time on the publisher during the snapshot phase, but at least we achieve our objective. Note that this is therefore never an issue in merge replication, which does not have a 'concurrent' option.

sp_changepublication @publication = 'yyy'
, @property = 'sync_method'
, @value = 'native'
, @force_invalidate_snapshot = 1

Note that there is another related error here which occurs in the RTM version of SQL Server 2005 but not the SP1 version. Namely, if you look in your snapshot files and see some FTI procedure calls, but not the essential sp_fulltext_column call, this is because you are using the default language setting in your FTI. In this case, select an explicit language on the publisher before initiating the snapshot.

Conclusions

Replicating of FTIs in transactional publications is currently slightly problematic in SQL 2005 but it is indeed possible by following the guidelines above.