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
- Install SQL Full Text Index service.
- 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
- 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
- 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
- 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).