Export (0) Print
Expand All

Backward Compatibility for Full-Text Search/Microsoft Search

SQL Server 2000

  New Information - SQL Server 2000 SP3.

The Microsoft Search catalog format is upgraded in Microsoft SQL Server 2000 Service Pack 3 (SP3) to improve consistency in the rank values returned by queries using the CONTAINSTABLE and FREETEXT full-text predicates.

Upgrading the Microsoft Search catalog format requires that all full-text catalogs be rebuilt. The rebuild is performed automatically when Microsoft Search service starts, immediately after SP3 setup completes, and may be a time- and resource-intensive process. During the rebuild, full-text functionality may be only partially available.

Note  The rebuild operation only occurs the first time that any instance of SQL Server 2000 on the computer is upgraded to Service Pack 3. If several instances of SQL Server 2000 exist on the same computer, all catalogs from all instances of SQL Server are rebuilt to the new format, not just the full-text catalogs associated with the instance of SQL Server that is being upgraded to SP3.

After SP3 is installed, the system event logs will contain messages stating that the catalogs were corrupt, were of an older version, and had to be rebuilt. Microsoft Search rebuilds these catalogs and upon completion returns another message about the success or failure of the rebuild operation.

If the catalog rebuild process does not complete successfully, the system event log will contain a message that the crawl stopped or failed. Crawl is the process of rebuilding and repopulation of the full-text catalogs. When the message states that a crawl failed, drop and recreate all full-text catalogs.

Here is an example of a log that illustrates these messages:

Event Type:   Information
Event Source:   Microsoft Search
Event Category:   Search Service 
Event ID:   1003
Date:      7/24/2002
Time:      5:26:51 PM
User:      N/A
Computer:   FTS8
Description:
The Search service has started.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type:   Information
Event Source:   MssCi
Event Category:   None
Event ID:   4138
Date:      7/24/2002
Time:      5:26:51 PM
User:      N/A
Computer:   FTS8
Description:
Content index corruption detected in component CI-RcovStorageObj1 in catalog e:\ft\SQL0000800005\Build\Indexer\NlFiles. Stack trace is
  0x01045027
  0x0104BE13
  0x010469E0
  0x0104D0CD
  0x01048477
  0x013BCE70
  0x013B9D9F
  0x013BA645
  0x009F4251
  0x009F450D
  0x0138CE4C
  0x0138E1D0
  0x00A32783
  0x00A2247A
  0x00A99965
  0x00AA5BDC
  0x77E8758A

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type:   Error
Event Source:   Microsoft Search
Event Category:   Indexer 
Event ID:   7039
Date:      7/24/2002
Time:      5:26:51 PM
User:      N/A
Computer:   FTS8
Description:
The Content Index for project <SQLServer SQL0000800005> cannot be loaded. Error: c0041821 - The content index data on disk is for the wrong version.  

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type:   Information
Event Source:   Microsoft Search
Event Category:   Gatherer 
Event ID:   3041
Date:      7/24/2002
Time:      5:26:51 PM
User:      N/A
Computer:   FTS8
Description:
Project <SQLServer SQL0000800005> is being reset.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type:   Information
Event Source:   Microsoft Search
Event Category:   Gatherer 
Event ID:   3018
Date:      7/24/2002
Time:      5:26:53 PM
User:      N/A
Computer:   FTS8
Description:
The end of crawl for project <SQLServer SQL0000800005> has been detected. The Gatherer successfully processed 0 documents totaling 0K. It failed to filter 0 documents. 0 URLs could not be reached or were denied access.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

For more information, see Knowledge Base article Q327217.

Types of Upgrade

To minimize the impact of a rebuild on database operations, consider the following variations to the upgrade process:

Default upgrade

A default upgrade takes place automatically after SP3 setup finishes. Use the default upgrade if the total size of all full-text catalogs is less than 100,000 rows and there are ample system resources available after SP3 setup.

Side-by-side upgrade

A side-by-side upgrade helps avoid significant periods in which full-text functionality will not be fully available during the rebuild process. This upgrade also minimizes resource usage after the upgrade to SP3. Use side-by-side upgrade if the total size of all full-text catalogs being upgraded is greater than 100,000 rows and the rebuild process may take up to an hour or more, depending on available hardware and system resources. For a complete side-by-side upgrade, all catalogs from all instances of SQL Server 2000 on the computer should be included. For more information, see Side-by-Side Upgrade, below.

Manual upgrade

A manual upgrade minimizes resource usage following upgrade to SP3 by allowing full-text catalogs to be added and populated during off-peak periods. However, the total amount of catalog downtime while manually rebuilding full-text catalogs may be greater than for a side-by-side upgrade. Perform a manual upgrade when it is more important for the server to be available soon after the SP3 upgrade, than it is for the full-text catalogs. For more information, see Manual Upgrade, below.

Side-by-Side Upgrade

A side-by-side upgrade requires a production computer and a backup development computer. The production computer is the computer running the instance of SQL Server to be upgraded; the backup development computer contains a replica of the production computer.

Before you upgrade, back up all SQL Server databases on the production computer. This backup will be used to create an image of the production computer on the development computer. For more information on creating and maintaining a development computer, see "Using Standby Servers" in SQL Server 2000 Books Online.

In addition, if change tracking is enabled on the production computer, disable background index update and any other scheduled updates. Leave intact any scheduled full or incremental populations.

To perform a side-by-side upgrade

  1. Restore the previously created database backup to the development computer. Ensure that the database identifiers are the same between the production computer and the development computer. For more information on how to move, copy, and back up full-text catalog folders and files, search the Knowledge Base.

  2. Because a database backup does not include full-text catalogs, execute the following system stored procedure for each full-text catalog so that the catalogs are created and populated on the development computer.
    EXEC sp_fulltext_catalog 'fulltext_catalog_name', 'rebuild'
    GO
    EXEC sp_fulltext_catalog 'fulltext_catalog_name', 'start_full'
    GO
    
  3. Apply SP3 to the development computer to upgrade the full-text catalog data.

  4. When the rebuild process completes on all full-text catalogs on the development computer, copy these full-text catalogs to a secure location on the production computer.

    Important  Do not overwrite existing full-text catalogs that are in the production computer at this time.

    For more information on how to move, copy, and back up full-text catalog folders and files, search the Knowledge Base.

  5. Apply SP3 to the production computer. After the upgrade to SP3 is complete, stop the Microsoft Search service.

    If the automatic rebuilding of the catalogs has started, stop the process for each catalog by executing the following system stored procedure.

    EXEC sp_fulltext_catalog 'fulltext_catalog_name', 'stop'
    GO
    
  6. On the production computer, replace existing catalogs with the rebuilt catalogs that were copied from the development computer.

  7. Start the Microsoft Search service.

    If change tracking is enabled on the production computer, manually update change tracking on all full-text enabled tables by executing the following system stored procedure.

    EXEC sp_fulltext_table '%tablename%', 'update_index'
    GO
    

    You do not need to rebuild any full-text catalog. In addition, if you turned off background index update or scheduled updates before starting the side-by-side upgrade, turn on these options.

Manual Upgrade

A manual upgrade selectively rebuilds full-text catalogs separately from the upgrade to SP3. 

Before you upgrade an instance of SQL Server on the production computer to SP3, execute the following system stored procedures and record the result set.

EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_columns

You will use the information returned by these stored procedures when you recreate full-text catalogs following the upgrade.

To perform a manual upgrade

  1. Drop all full-text indexed tables and full-text catalogs, in that order, using the sp_fulltext_table and sp_fulltext_catalog system stored procedures, respectively.

  2. Perform a full backup of SQL Server data.

  3. Apply SP3. During the setup process, ignore the warning message that all full-text catalogs will be rebuilt. (You already dropped these catalogs.)

  4. Recreate all full-text catalogs using the sp_fulltext_catalog system stored procedure.

  5. Add all previously indexed tables and columns to their original full-text catalogs, and start a full population on all full-text catalogs.

  6. For more information, see Knowledge Base article Q327217.
Show:
© 2014 Microsoft