Full-Text Search Manageability Enhancements

Microsoft SQL Server 2005 introduces a number of manageability enhancements to Full-Text Search that are described below.

SQL Server 2005 provides integrated backup and restore facilities for full-text catalogs. Earlier versions of SQL Server did not provide an integrated and reliable mechanism by which full-text catalogs could be backed up and restored. In this release of SQL Server, full-text catalogs can be backed up and restored along with, or separate from, database data. This functionality reduces the time necessary to recover from a disaster and simplifies the task of moving data, including full-text catalogs, from one computer to another without the need to fully repopulate the catalog. This backup and restore feature provides the following capabilities:

  • Ability to back up and restore one or more full-text catalogs to and from media in the same manner as other data.
  • Eliminates the need to fully repopulate data after a restore.
  • Updating of full-text data to reflect changes by rolling forward logs after a restore. Change tracking must be enabled for this capability.
  • For more information, see Backing Up and Restoring Full-Text Catalogs.

Full-Text Catalogs Included in Database Attach and Detach Operations

Microsoft SQL Server 2005 preserves full-text catalogs when administrators perform database detach and attach operations. Earlier versions of SQL Server ignored full-text catalogs during these operations, resulting in orphaned full-text catalogs. Administrators were forced to delete and rebuild the full-text catalog.

This version of SQL Server recognizes full-text catalogs as being part of the database. Administrators can detach a database, copy all database files to a new location, and then re-attach the database. Full-text catalogs are preserved in this process. For more information, see Attach and Detach Full-Text Catalogs.

SQL Server 2005 introduces a new XML data type that allows you to store an XML fragment or document. Full-text search in SQL Server now supports the creation of full-text indexes on, and full-text queries against, the XML data type.

Queries are at the granularity of the column value. Full-text predicates issued against a full-text indexed XML column return rows where the specified search string exists anywhere in the content of the column. For more information, see Querying varbinary(max) and xml Columns.

SQL Server 2005 Full-Text Search includes a major upgrade of Microsoft Search (MSSearch) service to version 3.0. Benefits of this upgrade are:

  • Massively improved full-text index population performance.
  • One instance of MSSearch 3.0 exists per instance of SQL Server.
  • MSSearch 3.0 runs under the same service account as SQL Server.
  • For more information, see Microsoft Full-Text Engine for SQL Server.

SQL Server 2005 Full-Text Search utilizes one instance of Microsoft Full-Text Engine for SQL (MSFTESQL) service per instance of SQL Server. Each instance of MSFTESQL starts and runs under the same service account as the SQL Server instance uses. If an administrator changes the SQL Server service account for a particular instance, the associated MSFTESQL service account is also updated. In earlier versions of SQL Server, one instance of MSSearch, running as LocalSystem, was shared across all instances and applications on the server.

Full-text search for SQL Server 2005 provides a number of rich status reporting enhancements offering easier manageability and supportability of full-text implementations. These enhancements include:

  • Population status logs that record status and indexing errors for each index within a catalog.
  • Additional service, catalog and index status, and configuration options available.
  • Exposure of full-text queries to SQL Server Profiler for diagnostic and performance analysis.

Community Additions