This article details the steps required to enable SQL Server full text search support within InstantKB & an on-premise instance of SQL Server 2008, 2012 or 2016.If you prefer you can also script the creation of your catalog and full text indexes. For further information on creating your catalog and indexes via TSQL script please see "Enabling Full-Text Search within InstantKB within an Azure SQL Database ".
Enabling Full -Text Search
If you've not already enabled full text search for your instance of SQL Server you can do so by executing the query below. If you right click your database tables and the "Full-Text Index" option is grayed out within the context menu you may need to execute the following query to enable full text search within your SQL Server database...
Creating your Catalog
Once you've enabled full-text search the next step is to create the catalog, Expand your InstantKB database within SQL Server Management Studio and click "Storage".Right click the "Full-Text Catalog" node and select "New Full-Text Catalog" as shown below...
This will popup the create catalog wizard. You can provide any name you wish for your catalog. We suggest leaving the majority of settings as the default when creating your catalog. You will have the option to amend catalog settings later.
Once you've created your catalog the next step is to add the database table columns we want to index into the catalog. For a complete list of columns that need to be included within the catalog please see the section below.
Defining the Full-Text Indexes / Columns To Index
You will need to right click on each table listed below and select the "Define Full-Text Index" option for each table listed below. This is shown in the following image...
You must go through each table listed below and define a full text index for each table. When creating the full text index you should include the columns for the table also listed below.
- InstantKB_Tickets.TicketTitle (only for InstantKB 2011)
- InstantKB_Tickets.TicketText (only for InstantKB 2011)
- InstantKB_TicketReplies.TicketText (only for InstantKB 2011-1)
The define index dialog is wizard based. You can use the default settings when creating each index however you must ensure you include the database table columns listed above.
Enabling Full-Text Search Within InstantKB
Once full-text is enabled within SQL Server and you've defined the indexes as shown above required by InstantKB you should enable full-text support within InstantKB. To enable full-text support within InstantKB you'll need to visit the Admin CP » Settings » Knowledgebase page as shown below and ensure the Search Type is set to either ContainsTable or FreeTextTable....
To learn more about the difference between ContainsTable & FreeTextTable please see the related links at the bottom of this article.
For further information on searching attachments stored within InstantKB please see "Searching Attachments witin InstantKB".
I hope this information helps. Please don't hesitate open a support request or contact us should you have any questions.