Export (0) Print
Expand All
1 out of 2 rated this helpful - Rate this topic

Full-Text Search Upgrade Options

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

Use the Full-Text Search Upgrade Options page of the SQL Server Installation Wizard to select the full-text search upgrade option to use for the databases that you are upgrading at this time.

In SQL Server 2005 and earlier versions, each full-text index resides in a full-text catalog that belongs to a filegroup, has a physical path, and is treated as a database file. Beginning in SQL Server 2008, a full-text catalog is a logical concept—a virtual object—that refers to a group of full-text indexes. Therefore, a new full-text catalog is not treated as a database file with a physical path. However, during upgrade of any full-text catalog that contains data files, a new filegroup is created on same disk. This maintains the old disk I/O behavior after upgrade. Any full-text index from that catalog is placed in the new filegroup if the root path exists. If the old full-text catalog path is invalid, the upgrade keeps the full-text index in the same filegroup as base table or, for a partitioned table, in the primary filegroup.

When you upgrade from SQL Server 2005 or SQL Server 2008 to SQL Server Code-Named “Denali”, choose one of the following full-text upgrade options.

Import

Full-text catalogs are imported. Typically, import is significantly faster than rebuild. For example, when using only one CPU, import runs about 10 times faster than rebuild. However, a full-text catalog imported from SQL Server 2005 does not use the new and enhanced word breakers that were introduced in SQL Server 2008, so you might want to rebuild your full-text catalogs eventually.

Note Note

Rebuild can run in multi-threaded mode, and if more than 10 CPUs are available, rebuild might run faster than import if you allow rebuild to use all of the CPUs.

If a full-text catalog is not available, the associated full-text indexes are rebuilt. This option is available for only SQL Server 2005 databases.

For information about the impact of importing full-text index, see "Considerations for Choosing a Full-Text Upgrade Option," later in this topic.

Rebuild

Full-text catalogs are rebuilt using the new and enhanced word breakers that were introduced in SQL Server 2008. Rebuilding indexes can take awhile, and a significant amount of CPU and memory might be required after the upgrade.

Reset

Full-text catalogs are reset. When upgrading from SQL Server 2005, full-text catalog files are removed, but the metadata for full-text catalogs and full-text indexes is retained. After being upgraded, all full-text indexes are disabled for change tracking and crawls are not started automatically. The catalog will remain empty until you manually issue a full population, after the upgrade completes.

All of these upgrade options ensure that upgraded databases benefit fully from the full-text performance enhancements of SQL Server 2008 and later versions.

Important note Important

For information about using new word breakers and upgrading noise-word files to stoplists after importing a SQL Server 2005 full-text index, see the topic “Full-Text Search Upgrade” in the Setup Help for SQL Server Code-Named “Denali”.

When choosing the upgrade option for your upgrade, consider the following:

  • How do you use word breakers?

    The full-text search service in SQL Server Code-Named “Denali” includes word breakers and stemmers that were introduced in SQL Server 2008. These might change the results of full-text queries from SQL Server 2005 for a specific text pattern or scenario. Therefore, how you use word breakers is important when choosing a suitable upgrade option:

    • If the word breakers of the full-text language you use did not change in SQL Server 2008 or later versions, or if recall accuracy is not critical to you, importing is suitable. Later, if you experience any recall issues, you can upgrade to the new word breakers simply by rebuilding your full-text catalogs. For more information, see the topic “Full-Text Search Upgrade” in the Setup Help for SQL Server Code-Named “Denali”.

    • If you care about recall accuracy and you use one of the word breakers that were improved in SQL Server 2008, rebuilding is suitable.

  • Were any full-text indexes built on integer full-text key columns?

    Rebuilding performs internal optimizations that improve the query performance of the upgraded full-text index in some cases. Specifically, if you have full-text catalogs that contain full-text indexes for which the full-text key column of the base table is an integer data type, rebuilding achieves ideal performance of full-text queries after upgrade. In this case, we highly recommend you to use the Rebuild option.

    Note Note

    For full-text indexes in SQL Server Code-Named “Denali”, we recommend that the column serving as the full-text key be an integer data type. For more information, see the topic “Performance Tuning and Optimization (Full-Text Search)” in SQL Server Code-Named “Denali” Books Online..

  • What is the priority for getting your server instance online?

    Importing or rebuilding during upgrade takes a lot of CPU resources, which delays getting the rest of the server instance upgraded and online. If getting the server instance online as soon as possible is important and if you are willing to run a manual population after the upgrade, Reset is suitable.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft. All rights reserved.