Database Mirroring and Full-Text Catalogs

  • Article

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 Preparing a Mirror Database for Mirroring.

Full-Text Catalog and Indexes Before Failover

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.

Full-Text Indexes After Failover

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

    Note

    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).

Note

Starting a crawl after failover works the same as starting a crawl after a restore.

After Forcing Service

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.

See Also

Concepts

Overview of Database Mirroring

Other Resources

ALTER FULLTEXT INDEX (Transact-SQL)
CREATE FULLTEXT INDEX (Transact-SQL)
DROP FULLTEXT INDEX (Transact-SQL)
How to: Remove a Full-Text Index from a Table (SQL Server Management Studio)
How to: Enable a Table for Full-Text Indexing (SQL Server Management Studio)
Administering Full-Text Search
Full-Text Search Architecture
Full-Text Search Fundamentals
Backing Up and Restoring Full-Text Catalogs

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • Revised the note in the automatic change tracking bullet.

17 July 2006
New content:
  • Revised the introduction to describe the difference between how mirroring handles catalog-level changes and index-level changes.
  • Removed the recommendation about considering a manual update of the full-text catalog.
Changed content:
  • Revised the description of synchronization of the full-text catalog after a fail over.