Step by Step Process to Add Full Text Search on SQL Server 2019


By:   |   Updated: 2021-05-31   |   Comments   |   Related: More > Full Text Search


Problem

Microsoft has developed a feature called Full Text Search in SQL Server to fulfill a requirement of fetching character-based data from tables with optimal performance. If you want to learn more about Full Text Search and its basic concepts, then I would recommend you to read my previous tutorial. In this article I will show how to add Full Text Search to an existing SQL Server instance to use for full text queries.

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 first install it, then create a Full Text Catalog followed by Full Text Indexes and then finally write your Full Text Queries using CONTAINS or FREETEXT operators to search specific words or phrases.

The first step of the implementation flow is the installation, so let’s install this component to an existing SQL Server instance where it is not already installed.

Full Text Search is not installed by default during SQL Server installation, you need to select it separately as additional feature during installation. If you are going to install a new SQL Server instance, then you don’t need to do much except click the checkbox near "Full-Text and Semantic Extractions for Search" option on the feature selection window in the SQL Server setup. This feature will be installed along with other components on your server. But if you have installed SQL Server without selecting the Full Text Search feature then you need to run the setup again to add this component to the existing SQL Server instance. Although this is additional feature, you cannot use it without using the SQL Server database engine.

If you want to check whether Full Text Search is installed in your SQL Server instance, then you can run the below T-SQL statement to get this information:

--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 feature is installed
  • 0 not installed
query results

You can see the Full Text Search component is not installed in our existing SQL Server instance so next we will go ahead and install it.

Full Text Search Installation on Existing SQL Server Instance

If the Full Text Search component is not installed along with SQL Server, then you will not be able to create Full Text Catalogs for any database. 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.
full text error

So, let's 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.

sql server 2019 setup full text search

The below popup window appears until the installation center is launched.

sql server 2019 setup full text search

Here is the installation center screen that will be used to install the full text search feature. Click "Installation" on the left side pane of the below screen.

sql server 2019 setup full text search

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

sql server 2019 setup full text search

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

sql server 2019 setup full text search

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

sql server 2019 setup full text search

Setup files will be installed as part of the prerequisites and once done the Next button will be enabled to proceed. Once the button is enabled, click the Next button.

sql server 2019 setup full text search

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

sql server 2019 setup full text search

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.

sql server 2019 setup full text search

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

sql server 2019 setup full text 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.

sql server 2019 setup full text search

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 will be installed as a separate service along with this installation. You can see this service configuration page is showing in the below screenshot. Click Next to go to next screen.

sql server 2019 setup full text search

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

sql server 2019 setup full text search

Installation will start.

sql server 2019 setup full text search

You can see the progress bar of the installation in the below screen. Wait until it is completed.

sql server 2019 setup full text search

Once Full Text Search is installed successfully then you will get the below screen that says this component has been successfully installed. You can click on the link for the log file for this installation if you want to see more details.

sql server 2019 setup full text search

Click the Close button to close the window.

Now Full Text Search has been installed for your SQL Server instance and you can go ahead and create Full Text Catalogs and Indexes based on your requirements.

You can run the below T-SQL statement to verify it was installed.

query results

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

sql server services

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.

query results
Next Steps

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



Last Updated: 2021-05-31


get scripts

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips



Comments For This Article





download





Recommended Reading

Expand search capabilities with the SQL Server FREETEXT command

Gathering status and detail information for SQL Server Full Text Catalogs

Full text indexing with Azure SQL database

T-SQL Script to Correct SQL Server Full Text Indexes Not Updating

Understanding SQL Server Full Text Search














get free sql tips
agree to terms