This article details how to enable and take advantage of SQL Server's full-text indexing service within InstantKB.NET v1.3. Information on how to schedule and maintain your full-text index catalog is also provided further within this document.
The SQL Server full-text indexing service when enabled provides advanced word or phrase searching including proximity searches, inflectional matches (drive = drove) and relevance ranking of results (how close the words are and quantity of matches).
Ensuring the Full-Text option is installed
SQL Server's full-text indexing is installed as a separate component feature through your SQL Server setup disk. This component is required in order for full-text searches to work. You can select the Full-Text Search service when prompted to select components during your SQL Server installation. This can also be added to an existing installation if required.
To determine if you have the full-text feature installed you should right click the small SQL Server icon within the task bar next to the date and time. This will pop up a context menu you will need to view the option that reads "Current service on [machinename]..." as below...
You will need the Microsoft Search feature installed for the full-text option to be available within enterprise manager. You should also ensure the Microsoft Search service (MSSearch) is set to auto-start each time your operating system restarts.
If your not sure if you have full-text indexing installed or would like information how to enable and install this feature you should follow the instructions within the links below.
Once your sure you have the correct full-text indexing service installed and enabled the next step is to create the full-text catalog for the InstantKB.NET database. This is best demonstrated with screen grabs to show the various steps within the full-text wizard.
This is a very simply point and click wizard process and should only take a couple of minutes to complete.
Creating the InstantKB.NET Full-Text Catalog
To begin creating the full-text catalog required for the full-text feature you should first select your database within enterprise manager. With your database selected click the "Tools" menu and the "Full-Text Indexing..." option. As shown below...
Selecting the "Full-Text Indexing..." option will popup a wizard as shown below.
Click next at this step. You will be prompted (as below) to select the table to create the full-text catalog for. You must select the InstantKB_Articles table to use for the catalog.
Once you select the InstantKB_Articles table and clicked Next you will be prompted to select which database fields to include within the SQL Server full-text catalog.
You should select the QuestionText, Keywords and AnswerText fields from the InstantKB_Articles table as shown below.
You have now specified which database table and columns to use within the catalog. The next step within the wizard is to enter a name for your catalog. You can name your catalog anything you wish this will not effect the application. This is shown below.
You have now created the catalog. Because the full-text catalog is maintained separately from your SQL Server database you will need to schedule how often you update your catalog and which method update method best suites your environment.
Whilst creating the catalog you will have the option to create a schedule (shown below) for your catalog however if your not sure which method to use it's reccommended you skip this step for the moment and read the Updating the Full-Text Catalog to decide how best to schedule population of your catalog.
If you choose to create a population schedule whilst creating your catalog the suggested method would be to schedule a full population for once a day during known low-traffic hours. You can create a schedule very easily. You simply need to click the "New Catalog Schedule" button and configure the options to suite your environment. An example is shown below.
Please remember you should ensure you know which method of population is best as discussed further below.
Click OK and Next to progress to the final wizard confirmation screen.
Once you click Finish the catalog will be created and you will see a small progress dialog. Once the catalog has been created you will be informed like below.
As this instructs you will now need to populate your catalog for the first time. This should always be a full population when first indexing the catalog. To do this you should visit the Full-Text Catalogs node within your database and right click the new catalog. This is shown below.
Once the catalog is populated the data within the knowledge base search will be available. Please remember if you add a new article or update any knowledge base content the catalog must be repopulated either manually or by the schedule to include the new updates.
Enable InstantKB.NET to use SQL Server Full-Text Search
Because of the specific requirements of the full-text functionality this feature is optional and disabled by default within the application. You can enable / disable the full-text functionality very easily from within the application settings page of the administration area. A screen grab is provided below showing how this should be enabled...
Once you enable full text searching you will have the option to use CONTAINS type searches or FREETEXT type searches. The CONTAINSTABLE and FREETEXTTABLE functions are used to specify full-text queries that return relevance rankings for each row. These functions are very similar but used differently from the full-text predicates, CONTAINS and FREETEXT.
CONTAINS can also be used with prefix terms (webserv*), proximity terms ("ASP" NEAR "DHTML"), generation (word form) terms, and weighted terms [Link_Description, ISABOUT("ASP" weight (.8), DHTML weight(.4)]
When FREETEXT is used, the full-text query engine internally "word-breaks" the freetext_string into a number of search terms and assigns each term a weight and then finds the matches.
Updating the Full-Text Catalog
There are two different ways you can update the catalog, by scheduled (full or incremental) populations or by tracking.
Use change tracking with the background update index option when CPU and memory are available, the value of an up-to-date index is high, and immediate propagation can keep up with the rate of changes.
Use change tracking with scheduled propagation when CPU and memory can be used at scheduled times, disk space for storing changes is available, and changes between the scheduled times are not so significant that the propagation takes longer than a full rebuild.
Use a full rebuild when a large percentage of records change or are added at once. If a large percentage of records change over an extended period of time, consider using change tracking with scheduled or background update index.
Use an incremental rebuild when a large number, but not a large percentage, of documents change at one time. If a large number of records change over an extended period of time, consider using change tracking with scheduled or background update index.
Full and incremental population:
- When setting the schedule, it is selected one or the other, and choose the times to populate. After creating you can edit any time by right-clicking on a catalog and choosing schedules.
- Start sql server agent by going to Management under the server, right clicking on sql server agent and selecting start. To ensure this keeps running, go to sql server agent-Properties-Advanced and select restart sql server and restart sql server agent if it stops unexpectedly.
- If you are using incremental population, all tables that apply must have a column of data type TimeStamp. SQL Server sets a TimeStamp column to an 8-byte integer value that’s guaranteed to be unique within a database and greater than any previously assigned value. It is automatically updated whenever you insert or update a row.
Tracking:
- Right click on the table, go to full-text index table, and select ‘Change Tracking’ and ‘Update Index in Background’. If both of these are selected then any time a change is made in the table sql server will automatically update the index. There is no need for sql server agent in this kind of tracking.
Comments:
Sql Server 7.0 does not have tracking as an option, so you must use population.
Summary
To take advantage of SQL Server full-text indexing within the knowledge base you will need the full-text search component installed. You will have created a catalog and specified the correct database columns.
You will have set a schedule for updating the catalog to ensure new content appears within the knowledge base search. You will have enabled the full-text option within the application by updating the application settings.
We hope this helps. If you do have any questions please use our support forums or post your request to support@instantasp.co.uk.