Configuring Microsoft SQL Server to perform full text searches in documents

This post describes the steps needed to install and configure Microsoft SQL Server to perform full text searches within documents like Word, pdf, rich text format etc. These instructions require no prior knowledge of using or installing SQL Server – all steps from installation to implementation are included.

1. Install SQL Server.

Go to MSDN Subscriber downloads:

https://msdn.microsoft.com/en-us/subscriptions/downloads/

If you have not yet done so create an account with them. Check the boxes that apply to you – 32/64 bit architecture, language etc. I chose the free version of SQL Server Express 2014 with Advanced Services. Advanced Series is what you will need if you wish to install full text searches. The ordinary installers not appear to include this.

Once downloaded, run the installer. Just click OK when prompted to choose the destination folder:

And let it run

Then choose to run a new installation of SQL Server 2014:

And continue through the installation (I’m re-installing on top of an existing installation so I can get the screenshots)

Click on ‘I Accept’

Click Next. When selecting features, make sure full-text is selected:

Select instance configuration. For selected instance chose default instance.

And finish by clicking Next of the remaining stages

The installation stage can take quite some time:

2. Download and run the semantic language database executable:

https://www.microsoft.com/en-gb/download/details.aspx?id=29069

3. Install SQL Server Management Studio 2014:

And run the setup application:

And complete the remaining steps by clicking on Next:

4. Check all pre-requisites are installed

Open SQL Server management Studio, and connect:

Select the ‘New Query’ button And enter the following SQL script:

SELECT SERVERPROPERTY('IsFullTextInstalled');
GO

If successfully installed you should get the following result displayed:

Then run the following new query:

SELECT * FROM sys.fulltext_semantic_language_statistics_database;
GO

If the semantic language database successfully installed you should get something like this:

Do not proceed until the above two steps are completed successfully.

5. Create your database

In Windows Explorer create a folder called ‘FileTable’:

In SQL Server management studio select new query and run the following script:

CREATE DATABASE FileSearchTest
ON PRIMARY
(
    NAME = N'FileSearchTest',
    FILENAME = N'C:\FileTable\FileSearchTest.mdf'
),
FILEGROUP FilestreamFG CONTAINS FILESTREAM
(
    NAME = MyFileStreamData,
    FILENAME= 'C:\FileTable\Data'
)
LOG ON
(
    NAME = N'FileSearchTest_Log',
    FILENAME = N'C:\FileTable\FileSearchTest_log.ldf'
)
WITH FILESTREAM
(
    NON_TRANSACTED_ACCESS = FULL,
    DIRECTORY_NAME = N'FileTable'
)

Database ‘FileSearchTest’ created as shown

Then run the next script to create the database table:

USE FileSearchTest
GO
CREATE TABLE DocumentSemantics AS FileTable
WITH
(
    FileTable_Directory = 'DocumentSemantics',
    FileTable_Collate_Filename = database_default
);
GO

Which create the ‘DocumentSemantics’ table:

This file table is where we will store all our word documents.

Right click on this table and select Explore FileTable Directory:

In the explore Window that appears, use this to paste the word documents you have selected:

Like so:

Run the following SQL command to see all the file details:

SELECT * FROM dbo.DocumentSemantics