Microsoft Full Text Engine for SQL Server 2005

By:   |   Updated: 2006-10-17   |   Comments   |   Related: More > Full Text Search

Problem
Finding information in large text or Binary Large Object (BLOB) columns is a daunting task. Using the LIKE keyword, although effective in most cases, will not always find the needed information. Full-text searching and indexing is a useful yet under-utilized feature of SQL Server that assists developers and users in finding the information. Versions prior to 2005 use a shared service to provide full-text functionality, making the process inefficient. The backup and restore process was also tedious and separate from the usual database backup process. In SQL Server 2005 Microsoft designed a new version of the engine, called Microsoft Full-Text Engine for SQL Server (MSFTESQL) to make full-text searching and indexing more efficient and easier to maintain.

Solution
Full-Text Search Concepts
In general, full-text searching technologies use a number of components to accomplish its mission. Word breaker is a component that determines where a word begins and ends. A stemmer determines the root form of a given word (strands and stranded =strand). In addition, they also take advantage of protocol handlers (i.e., HTTP and MAPI); a gatherer to manage the URL queue; and filters that actually extract textual information from the source. Full-text searching is used in products other than Microsoft SQL Server (Microsoft Exchange Server, Microsoft SharePoint technologies, and the Indexing Service for Microsoft Windows Server), to find the following:

  • Text data in text, ntext, char, nchar, varchar, and nvarchar columns
  • Text data stored in a column with data type image
  • Text data in Word documents, Web sites, Excel documents, text files, and PowerPoint presentations (these are native filters in SQL Server, although more can be added)

The MSSEARCH Service
In SQL Server 7.0 and 2000, Full-Text Searching and Indexing is handled by the MSSEARCH service. Only one instance of the MSSEARCH Service is installed and run on a server regardless of the number of instances of SQL Server installed. This means that the service is shared with all other components on the server that use the MSSearch service. It must run under the Local System context so that it can keep track of the MSSQLServer service account and handle modification of the full-text catalogs, since catalogs are stored outside of the database files. Once installed no modification can be made to the service, nor can changes be made to the MSSQLServer account except through the server properties of Enterprise Manager.

The MSFTESQL Service
The MSFTESQL Service (Microsoft Full-Text Engine for SQL Server) in SQL Server 2005 is a new and improved version based on the Microsoft Search Service. For each instance of SQL Server 2005 installed on the server there is an instance of the MSFTESQL service. This allows exclusive use of the service by a SQL Server instance. The pictures below show the running services in SQL Server Configuration Manager on different computers:

Services on computer running one SQL2000 and one SQL2005 instance Services on computer running two SQL2005 instances (Developer and Enterprise)

Unlike MSSEARCH, the MSFTESQL Service does not have to run under the Local System account context; it does, however, have to run under the same security account as the MSSQLServer service, since they work hand-in-hand and need to keep track of one another.

Next Steps

  • If you are looking for a new means to search your data, consider researching Full Text Search to incorporate more than just a literal search or using a LIKE clause.
  • Keep in mind that full text search products are also available for files such as Word, Excel, etc., so consider researching these technologies.
  • Review information on Microsoft Search Service and Microsoft Full-Text Engine for SQL Server
  • Download the sample AdventureWorks database and work with the full-text search concepts.
  • Tune in to MSSQLTIPS for more information on querying using Full-Text Search


Last Updated: 2006-10-17


get scripts

next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools