Step by Step Process to Add Full Text Search on SQL Server 2019
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.
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
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.
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.
The below popup window appears until the installation center is launched.
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.
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 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.
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.
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.
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 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.
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.
Installation will start.
You can see the progress bar of the installation in the below screen. Wait until it is completed.
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.
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.
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.
Explore Full Text Search to allow for text-based searches for your databases.
- Read more articles about Full Text Search
Last Updated: 2021-05-31
About the author
View all my tips