Add Full Text Search on SQL Server

Problem

Microsoft has developed a feature called Full Text Search in SQL Server to optimize text searches. To learn more about Full Text Search basics, read my previous tutorial. In this article, I will show how to add Full Text Search to an existing SQL Server instance.

Solution

As mentioned in my last article, there are a sequence of steps we should follow to use full text search. The sequence was to:

• Install Full Text Search
• Create a Full Text Catalog
• Create Full Text Indexes
• Write your Full Text Queries using CONTAINS or FREETEXT operators to search specific words or phrases.

SQL Server installations do not install Full Text Search by default. You need to select it separately as an additional feature. For a new SQL Server instance, click the checkbox “Full-Text and Semantic Extractions for Search” on the feature selection window. This feature is installed with other server components. If you installed SQL Server without Full Text Search, you need to run setup again to add this component. Although this is additional feature, you cannot use it without using the SQL Server database engine.

T-SQL command to determine Full Text Search installation status:

--Check whether Full text Search is Installed or not
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [Full Text Search Installed];  
GO

The output value “IsFullTextInstalled” will return:

• 1 = Installed
• 0 = Not installed

Full Text Search is not installed. Next, we will install it.

Full Text Search Installation on Existing SQL Server Instance

You can see the below errors while creating a Full Text Catalog on a SQL Server instance that is running without the full text search feature.

Msg 7609, Level 17, State 5, Line 3518
Full-Text Search is not installed, or a full-text component cannot be loaded.

Install Full Text Search

Copy the SQL Server setup files on the server where you want to install it or add it to existing installation. Right click the SQL Server setup file and select “Run as Administrator…” to launch the installation center.

The popup window will appear as the Installation Center loads.

Installation Center Screen

Install Full Text Search from the SQL Server Installation Center. Click “Installation” on the left side pane of the below screen.

Then click the first option “New SQL Server stand-alone installation or add features to an existing installation”.

Again, you will get a popup window until the SQL Server setup wizard appears.

You will get the below screen to proceed with installation. Click the Next button to go to next screen.

Setup files are installed as a prerequisite. Click the Next button, once enabled.

The next screen will check install rules. All rules must pass for a successful installation. You can ignore warnings but must not ignore any errors. Click the Next button to go to next screen.

Add Features

The next screen will allow you to choose an existing instance on which you need to enable Full Text Search by installing its supported components. Click the second radio button shown screen.

You can see, I have selected option “Add feature to an existing instance of SQL Server 2019”. Now click the Next button to proceed.

Full-Text and Semantic Extractions for Search

This is the main screen where you need to select the target feature that we want to install.  I clicked the check box besides this option in the below screen and clicked Next to proceed.

As we know from our previous article, full text search is based on two processes one is the SQL Server engine sqlservr.exe and the other is fdhost.exe. The second process fdhost.exe is installed as a separate service. You can see this service configuration page is showing in the below screenshot. Click Next to go to next screen.

Now, you will get a final window for validation. You can validate all configurations.  Once satisfied with all settings and configurations then click the Install button to start the Full Text Search installation.

Installation will start.

You can see the progress bar of the installation in the below screen. Wait for completion.

Full Text Search is installed. Click on the link for the log file for this installation if you want to see more details.

Click the Close button to close the window.

Create Full Text Catalogs

Create your first Full Text Catalogs and Indexes based on your requirements.

Run the below T-SQL statement to verify installation.

You can also validate the install by looking at the Windows services and you can see the new service which is highlighted below.

Although all user databases will be enabled to use Full Text Search post installation, if you get any errors using this feature for a database, you can use the below query to make sure it is enabled for the database you are trying to use for Full Text Search.

Next Steps

Explore Full Text Search to allow for text-based searches for your databases.

• Read more articles about Full Text Search

Manvendra Singh

Manvendra is a passionate database lover currently working as a Technical Architect at one of the top MNCs. He has extensive experience on all versions of SQL Server since 2000 and has built Big Data skills.  He has lead projects on the following database features: Database Mirroring, Always ON, Replication, Log Shipping, Geo-Clustering and Performance Tuning and delivered many SQL Server projects on consolidation, upgrades, heterogeneous replication, HA / DR solutions, automation and major performance tuning projects.  You can also follow Manvendra on his blog, Twitter, LinkedIn and Facebook.

• MSSQLTips Awards: Champion (100+ tips) – 2018 | Author Contender – 2015-2018, 2021-2022