Optionally provide private feedback to help us improve this article...

Thank you for your feedback!


Enabling Full-Text Search within InstantKB within a Azure SQL Database

New

If you're installing InstantKB within an Azure SQL database and wish to take advantage of the full-text search capabilities offered by InstantKB you will need to ensure you programmatically create the full-text catalog and add the necessary indexes into this category.

The following TSQL code should allow you to create the full-text catalog and add all necessary indexes to this catalog within your on-premise SQL Server instance or within an Azure SQL database. You will of course first need to connect to your SQL database via a tool such as SQL Server Management Studio to execute this query against your database.

It's important to note you should ensure you have your InstantKB database selected before executing the query. If you don't have the InstantKB database selected you will likely receive errors as the objects we are trying to index will not exist.

-- delete category if it already exists and drop indexes
IF EXISTS(SELECT name FROM sys.fulltext_catalogs WHERE name = 'InstantKBCatalog')
BEGIN

IF EXISTS(SELECT COUNT(*) FROM sys.fulltext_indexes WHERE object_id = object_id('InstantASP_Tags'))
BEGIN
PRINT 'Dropping index InstantASP_Tags';
DROP FULLTEXT INDEX ON InstantASP_Tags;
PRINT 'Dropped index InstantASP_Tags';
END
IF EXISTS(SELECT COUNT(*) FROM sys.fulltext_indexes WHERE object_id = object_id('InstantKB_ArticleComments'))
BEGIN
PRINT 'Dropping index InstantKB_ArticleComments';
DROP FULLTEXT INDEX ON InstantKB_ArticleComments;
PRINT 'Dropped index InstantKB_ArticleComments';
END
IF EXISTS(SELECT COUNT(*) FROM sys.fulltext_indexes WHERE object_id = object_id('InstantKB_Articles'))
BEGIN
PRINT 'Dropping index InstantKB_Articles';
DROP FULLTEXT INDEX ON InstantKB_Articles;
PRINT 'Dropped index InstantKB_Articles';
END
IF EXISTS(SELECT COUNT(*) FROM sys.fulltext_indexes WHERE object_id = object_id('InstantKB_Tickets'))
BEGIN
PRINT 'Dropping index InstantKB_Tickets';
DROP FULLTEXT INDEX ON InstantKB_Tickets;
PRINT 'Dropped index InstantKB_Tickets';
END
IF EXISTS(SELECT COUNT(*) FROM sys.fulltext_indexes WHERE object_id = object_id('InstantKB_TicketReplies'))
BEGIN
PRINT 'Dropping index InstantKB_TicketReplies';
DROP FULLTEXT INDEX ON InstantKB_TicketReplies;
PRINT 'Dropped index InstantKB_TicketReplies';
END

-- drop our catalog
PRINT 'Dropping catalog InstantKBCatalog';
DROP FULLTEXT CATALOG InstantKBCatalog
PRINT 'Dropped catalog InstantKBCatalog';

END

-- create catalog

PRINT 'Creating catalog InstantKBCatalog';
CREATE FULLTEXT CATALOG InstantKBCatalog AS DEFAULT;
PRINT 'Created catalog InstantKBCatalog';

-- add indexes

--CREATE UNIQUE INDEX PK_InstantASP_Tags_TagID ON InstantASP_Tags(TagID);

PRINT 'Creating index InstantASP_Tags';
CREATE FULLTEXT INDEX ON InstantASP_Tags
(TagKeyword, TagKeywordEncoded LANGUAGE 1033)
KEY INDEX PK_InstantASP_Tags_TagID
ON InstantKBCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantASP_Tags';

PRINT 'Creating index InstantKB_ArticleComments';
CREATE FULLTEXT INDEX ON InstantKB_ArticleComments
([Comments] LANGUAGE 1033)
KEY INDEX PK_InstantKB_ArticleComments_CommentID
ON InstantKBCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantKB_ArticleComments';

PRINT 'Creating index InstantKB_Articles';
CREATE FULLTEXT INDEX ON InstantKB_Articles
(ArticleTitle, ArticleText LANGUAGE 1033)
KEY INDEX PK_InstantKB_Articles_ArticleID
ON InstantKBCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantKB_Articles';

PRINT 'Creating index InstantKB_Tickets';
CREATE FULLTEXT INDEX ON InstantKB_Tickets
(TicketTitle, TicketText LANGUAGE 1033)
KEY INDEX PK_InstantKB_Tickets_TicketID
ON InstantKBCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantKB_Tickets';

PRINT 'Creating index InstantKB_TicketReplies';
CREATE FULLTEXT INDEX ON InstantKB_TicketReplies
(TicketText LANGUAGE 1033)
KEY INDEX PK_InstantKB_TicketReplies_TicketReplyID
ON InstantKBCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantKB_TicketReplies';

-- start population

PRINT 'Enabling indexes';

ALTER FULLTEXT INDEX ON InstantASP_Tags ENABLE; 
ALTER FULLTEXT INDEX ON InstantKB_ArticleComments ENABLE; 
ALTER FULLTEXT INDEX ON InstantKB_Articles ENABLE; 
ALTER FULLTEXT INDEX ON InstantKB_Tickets ENABLE; 
ALTER FULLTEXT INDEX ON InstantKB_TicketReplies ENABLE; 

GO 

PRINT 'Starting full population';

ALTER FULLTEXT INDEX ON InstantASP_Tags START FULL POPULATION;
ALTER FULLTEXT INDEX ON InstantKB_ArticleComments START FULL POPULATION;
ALTER FULLTEXT INDEX ON InstantKB_Articles START FULL POPULATION;
ALTER FULLTEXT INDEX ON InstantKB_Tickets START FULL POPULATION;
ALTER FULLTEXT INDEX ON InstantKB_TicketReplies START FULL POPULATION;

PRINT 'You can safely ignore any warnings that indicate population is currently active.';

Localization

You'll notice in the example code above we specify a language when creating the full text index via the following code "LANGUAGE 1033". This language is important as it's used by SQL Server to perform word breaking, infection and determine which stopword list to use. If the majority of your InstantKB articles are within a language other than English you may wish to change the language code above within the example above before executing this code. You can find a complete list of supported language codes within the MSDN documentation here.

That's It!

We hope this example helps you setup full text search either on-premise or within your Azure SQL database. Of course should you have any questions or run into any issues please don't hesitate to open a support ticket.