Database Mirroring and Full-Text Catalogs (SQL Server)
Applies To: SQL Server 2016
To mirror a database that has a full-text catalog, use backup as usual to create a full database backup of the principal database, and then restore the backup to copy the database to the mirror server. For more information, see Prepare a Mirror Database for Mirroring (SQL Server).
In a newly created mirror database, the full-text catalog is the same as when the database was backed up. After database mirroring starts, any catalog-level changes that were made by DDL statements (CREATE FULLTEXT CATALOG, ALTER FULLTEXT CATALOG, DROP FULLTEXT CATALOG) are logged and sent to the mirror server to be replayed on the mirror database. However, index-level changes are not reproduced on the mirror database because it is not logged on to the principal server. Therefore, as the contents of the full-text catalog change on the principal database, the contents of the full-text catalog on the mirror database are unsynchronized.
After a failover, a full crawl of a full-text index on the new principal server might be required or useful in the following situations:
If change-tracking is turned OFF on a full text index, you must start a full crawl on that index by using the following statement:
ALTER FULLTEXT INDEX ON table_name START FULL POPULATION
If a full-text index is configured for automatic change tracking, the full-text index is automatically synchronized. However, synchronization slows full-text performance somewhat. If performance is too slow, you can cause a full crawl by setting change tracking off and then resetting it to automatic:
To set change tracking off:
ALTER FULLTEXT INDEX ON table_name SET CHANGE_TRACKING OFF
To set on automatic change tracking to automatic:
ALTER FULLTEXT INDEX ON table_name SET CHANGE_TRACKING AUTO
To see whether auto change tracking is on, you can use the OBJECTPROPERTYEX function to query the TableFullTextBackgroundUpdateIndexOn property of the table.
For more information, see ALTER FULLTEXT INDEX (Transact-SQL).
After service is forced to the mirror server (with possible data loss), start a full crawl. The method to use for starting a full crawl depends on whether the full-text index is change tracked. For more information, see "Full-Text Indexes After Failover," earlier in this topic.