This article details the steps required to enable SQL Server full text search support within InstantKB.NET & SQL Server 2005. Towards the bottom of this article you'll also find information on how to enable InstantKB.NET to search attachments and also how to install 3rd party iFilters to search PDF documents.
If you've not already enabled full text search for your instance of SQL Server 2005 you can do so by executing the query below. If your using SQL Server 2000 you'll need to ensure you install the full-text service from the add / remove programs dialog.
To enable full text search within SQL Server 2005 execute the following query within SQL Server 2006 Management Studio...
sp_fulltext_database 'enable'
Once you've enabled full-text search the next step is to create the catalog, Expand your InstantKB.NET database within SQL Server 2005 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 to the catalog. For a complete list of column that need to be included within the catalog please see the section below.
You will need to right click on each table listed below and select the "Define Full-Text Index" option for each table listed below.
IMPORTANT: You must always include the following database columns within your full-text catalog.
You will need to define full-text indexes for the following columns...
************************************************
- InstantASP_Tags.TagKeyword
- InstantASP_Tags.TagKeywordEncoded
- InstantKB_ArticleComments.Comments
- InstantKB_Articles.ArticleTitle
- InstantKB_Articles.ArticleText
************************************************
You will need to define an index for each column listed above. 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.
Once full-text is enabled within SQL Server and you've defined the indexes as shown above required by InstantKB.NET you should enable full-text support within InstantKB.NET. To enable full-text support within InstantKB.NET you'll need to visit the Admin CP » Settings » Knowledgebase page as shown below...
To learn more about the difference between ContainsTable & FreeTextTable please see the related links at the bottom of this article.
To search attachments within InstantKB.NET you must ensure the following...
- Full-Text must be enabled - Full-Text Search must already be enabled & working as shown above.
- Attachments must be stored within the database - This is the default setting so you may not need to make any changes here however you should double check attachments are being stored in the database. You can check this from the "Admin CP » Settings » Knowledgebase" page as shown below. This is required to ensure InstantKB.NET saves article attachments to the InstantKB_Attachments database table. You'll need to define a full-text index on this table as described below these steps.
- Check the "Search Attachments" checkbox - from the "Admin CP » Settings » Knowledgebase" page as shown below...
These changes will ensure InstantKB.NET attempts to also search the contents of your attachments during any keyword search. The next step is to actually now include the Attachment database field within the full-text catalog we previously created. This is detailed in the next section.
If you enable searching of attachments within the "Admin CP » Settings » Knowledgebase" Settings you'll need to ensure attachments are saved to the database and that you include the following columns within the full text catalog...
************************************************
- InstantKB_Attachments.AttachmentBlob
************************************************
You'll need to also select a language breaker as it's a binary field. You should choose the language the majority of your attachments will be written in. The suggested settings for this index are shown below...
The SQL Server 2005 full-text search supports searching the following file types out-the-box...
- ASP, ASPX, CSS, HTM, HTML, HHT, HTW, HTX, ODC, STM, EML
- DOC, DOT, POT, PPS, PPT, XLB, XLC, XLS, XLT, RTF
- TXT, ASM, BAT, C, CPP, CXX, CMD, DEF, DIC, H, HPP, XML
To index Office 2007 documents please follow the steps in the following knowledgebase article...
http://support.microsoft.com/kb/945539
You can search additional file types such as PDF files or CHM files through the use of 3rd party SQL Server iFilters. Installing an iFilter is straight-forward and no additional configuration is required within InstantKB.NET once the iFilter has been installed.
Providing the "InstantKB_Attachments.AttachmentBlob" column is included within your fulltext catalog as detailed above the content types covered by the iFilter will automatically be included within InstantKB.NET search results as the catalog updates.
To assist with the installation of iFilters we'll walk through a quick example below of how to install the Adobe PDF iFilter to ensure the contents of PDF files are also searched from within InstantKB.NET.
For a complete list of 3rd party IFilters please see the related links section below.
You will need to install the Adobe PDF iFilter on your SQL Server 2005 machine. You can download the free Adobe PDF iFilter from the link below. Installation is wizard based and no configuration is required...
http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611
Once the installation is complete use the following query within SQL Server 2005 Management Studio to determine if the installation was successful.
select document_type, path from sys.fulltext_document_types where document_type = '.pdf'
If everything was successful within the results panel you should see...
This indicates the installation was successful. As your catalog updates you should see results returned for your new content type.
- On the computer, stop and restart the "msftesql" service, or stop and restart the "msftesql$instancename" service. To do this, use one of the following methods. You can also do this from the surface area configuration utility as details in the "Restarting Full-Text Search Service" section below.
Stop and restart the msftesql service.
a. At a command prompt, type net stop msftesql, and then press ENTER.
b. At a command prompt, type net start msftesql and then press ENTER.
Stop and restart the msftesql$instancename service.
At a command prompt, type net stop msftesql$instancename, and then press ENTER.
b. At a command prompt, type net start msftesql$instancename and then press ENTER.
Note The instancename placeholder represents the instance on which you want to enable the IFilter.
- Click Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration.
- In the pop-up SQL Server 2005 Surface Area Configuration dialog box, please click to select Surface Area Configuration for Services and Connections.
- Select MSSQLSERVER > Service, and click the Stop button and then Start.
- Select Full-Text Search > Service, and click the Stop button and then Start.
Once you've created your catalog and defined the indexes to include in that catalog you should ensure you set a population schedule for the catalog. This will ensure your search results remain up-to date and new content is frequently added to the search catalog.
To define a population schedule you'll need to visit your catalog properties as shown below...From within the catalog properties dialog you should click the "Population Schedule" option and then "New" to create your population schedule.
Creating the population schedule is very similar to creating a calendar item within outlook and very straight-forward. We won't cover the options available here. We would suggest you populate your catalog during low traffic times. How often you update is really down to how often your content changes. We would suggest updating the catalog at least once a day.
I hope this information helps. If you have any comments or questions please don't hesitate to use the comments below. I'll do my best to respond as quickly as possible. You can of contact support should you have any questions.