How to use MS SQL server full text search to index file content?

By June 29th 2016 8:58 am
MS SQL featured image

The problem

We have :

  • A SQL table with records corresponding to files
  • The files stored in a folder of the filesystem

The table contains information about the files like :

  • Language
  • Validity
  • Dates
  • Title

To search in the files, the system needs to use the information stored in the table and the files content. The system returns only the files containing the keyword searched for but the files are also filtered depending of the information stored in the table. (Only the valid documents in a selected language).

Old solution

In the past, for multiple customers, index server (after renamed indexing service) was used to index the file contents.

In MSSQL server, it’s possible to query the index server in a SQL query with those concepts :

  • Linked servers (to create a link between SQL server and Index server)
  • Openquery (to query the index server catalog)

All the search are done using stored procedures combining all those elements.

New solution

I propose to work with the Full text search functionality proposed by SQL server. A constraint of this system is that all the files must be stored in the database. But in the last versions of SQL server, it’s possible to store only a link to the file stored in the filesystem. This solution avoids a too big database.

How to put this solution in place?

  1. Activate the Full text search
  2. Enabling FILESTREAM on an existing instance
  3. Enabling FILESTREAM on an existing database
  4. For each table:
    1. Enabling FILESTREAM on an existing table
    2. Creating a full text index on the new column
    3. Populating the new columns
  5. Updating the SP