SQL Full Text Search Setup for Job Discovery

SQL Full Text Search is now enabled through a new job source option “UseFullTextSearch” in Simply Migrate to vastly improve the performance of discovery and filter type jobs.

Note that for now (Jan 2017) this applies to previous job source type jobs only.

The Full Text Search option can be enabled as follows:

PS C:\> New-SMJob -Source Job -SourceInput 10 -SourceOptions UseFullTextSearch ...

However before creating a job with this option note that it requires that the SQL Full Text Indexing service is installed and configured in your SQL Server, the remainder of this article details the steps required for setting this up.

Step-by-step setup guide

  1. Install SQL Full Text Index service.
image2017-1-13_11-14-57
Install SQL Full Text Search feature
  1. Create Full Text Catalog in the SQL server database by running the following SQL script from SQL Management Server:
USE SimplyMigrate
GO
 
CREATE FULLTEXT CATALOG JobEntries_FTCatalog
GO
  1. Now create the Full Text Index for JobEntries.
CREATE FULLTEXT INDEX ON JobEntries
(
    Author
    Language 1033,
    Recipients
    Language 1033
)
KEY INDEX [PK_dbo.JobEntries] ON JobEntries_FTCatalog
WITH CHANGE_TRACKING MANUAL
GO
  1. Wait for index population to complete. The following script can be useful to monitor the status of the the index population, when complete the status will show “Idle”.
-- SQL Script to check index population progress
DECLARE @CatalogName VARCHAR(MAX) = 'JobEntries_FTCatalog'
SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs AS cat
  1. Finally and optionally configure the change tracking as per requirements, e.g. auto or scheduled. Note the default is manual which will NOT update the index after the initial population.

The resource requirements of this full text index should be reviewed with your SQL database administrator, but by scheduling or manually updating the index only when required the impact on running jobs can be easily managed.

Note: Without the full text index using the source option “UseFullTextSearch” will result in incorrect results for SourceFilter as only the results in the index will be returned (ie no error will occur if 0 items are in the index).

No Comments

Be the first to start a conversation

Leave a Comment