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

Thank you for your feedback!


Enabling Full-Text Search with InstantForum within a Azure SQL Database

New

If you're installing InstantForum within an Azure SQL Database you cannot use the graphical user interface tools within SQL Server Management Studio to create your full text catalog and add the necessary indexes to this catalog. Instead you will need to script this process via TSQL code.

To assist with enabling full-text search within an Azure SQL Database we've provided TSQL script below that will create a new full-text catalog and add the necessary indexes for your InstantForum database.

-- rename incorrectly named constraint (for earlier versions of InstantForum)
EXEC sp_rename 
@objname = '[InstantASP_Tags].[PK_InstantASP_Tags.TagID]',
@newname = 'PK_InstantASP_Tags_TagID',
@objtype = 'OBJECT'
GO

PRINT 'If you see an "@objname is ambiguous" error above this can safely be ignored. This simply means your schema is upto date and already correct.';

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

IF EXISTS(SELECT object_id 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 object_id FROM sys.fulltext_indexes WHERE object_id = object_id('InstantForum_Topics'))
BEGIN
PRINT 'Dropping index InstantForum_Topics';
DROP FULLTEXT INDEX ON InstantForum_Topics;
PRINT 'Dropped index InstantForum_Topics';
END
IF EXISTS(SELECT object_id FROM sys.fulltext_indexes WHERE object_id = object_id('InstantForum_Messages'))
BEGIN
PRINT 'Dropping index InstantForum_Messages';
DROP FULLTEXT INDEX ON InstantForum_Messages;
PRINT 'Dropped index InstantForum_Messages';
END

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

END

-- create catalog

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

-- add indexes

IF NOT EXISTS(SELECT object_id FROM sys.fulltext_indexes WHERE object_id = object_id('InstantASP_Tags'))
BEGIN
PRINT 'Creating index InstantASP_Tags';
CREATE FULLTEXT INDEX ON InstantASP_Tags
(TagKeyword LANGUAGE 1033)
KEY INDEX PK_InstantASP_Tags_TagID
ON InstantForumCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantASP_Tags';
END
IF NOT EXISTS(SELECT object_id FROM sys.fulltext_indexes WHERE object_id = object_id('InstantForum_Topics'))
BEGIN
PRINT 'Creating index InstantForum_Topics';
CREATE FULLTEXT INDEX ON InstantForum_Topics
(Title LANGUAGE 1033)
KEY INDEX PK_InstantForum_Topics_PostID
ON InstantForumCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantForum_Topics';
END
IF NOT EXISTS(SELECT object_id FROM sys.fulltext_indexes WHERE object_id = object_id('InstantForum_Messages'))
BEGIN
PRINT 'Creating index InstantForum_Messages';
CREATE FULLTEXT INDEX ON InstantForum_Messages
([Message] LANGUAGE 1033)
KEY INDEX PK_InstantForum_Messages_PostID
ON InstantForumCatalog
WITH STOPLIST = SYSTEM
PRINT 'Created index InstantForum_Messages';
END

-- start population

PRINT 'Enabling indexes';

ALTER FULLTEXT INDEX ON InstantASP_Tags ENABLE; 
ALTER FULLTEXT INDEX ON InstantForum_Topics ENABLE; 
ALTER FULLTEXT INDEX ON InstantForum_Messages ENABLE; 

GO 

PRINT 'Starting full population';

ALTER FULLTEXT INDEX ON InstantASP_Tags START FULL POPULATION;
ALTER FULLTEXT INDEX ON InstantForum_Topics START FULL POPULATION;
ALTER FULLTEXT INDEX ON InstantForum_Messages START FULL POPULATION;

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

GO

Localization

You'll notice in the example code above we specify a language when creating the full text indexes via the following code "LANGUAGE 1033". This language is important as it's used by SQL Server to perform word breaking, inflection and determine which stopwords to use. If the majority of your InstantForum posts 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 examples help you configure full-text search within your Azure SQL Database for InstantForum. Of course should you encounter any issues or have any questions please don't hesitate to submit a support request.