Problem: How to resolve TF400644 with exotic languages

Whenever you want to create a new Team Project Collection in Azure DevOps Server 2019 (this is the on-premise version, we named it shortly AzDOS), you have to check several compatibility and readiness conditions during this phase.

One of these checks related to the data-tier, which has (as its names imply) a strong relationship with the central database server of AzDOS. This server is Microsoft SQL Server, the version depends (nearly) on your choice. You can find the full list here.

You’ve probably heard about the database collation, the role of this feature within SQL Server. We will come across this setting during the above creation process, and we won’t able to pass it. Like this:

TF400644 occurred, but why?

Reason

Our collation setting was CI_AS, which is a very “language-neutral” setting. We set this value because we want to store most of the written information (WIKIs, descriptions of the product backlog items, etc.) in AzDOS in a non-English language. At the same time, we want to use the enhanced function of the new search engine in AzDOS, which is based on SQL Server full-text and Elastic Search capabilities.

How can I persuade the AzDOS’s Team Project Collection “wizard” to use my predefined SQL Server collation? The key thing is the IFilter pack.

The language tools which are used by the SQL Server aren’t related to the SQL Server itself, rather than the IFilter packs in Windows. These kinds of packs (or plugins) most of the time are downloadable and shipped with other Microsoft Office products. There are separated plugins for the different kind of files, e.g. *.txt, *.docx, *.pdf, etc.

Solution

The only thing we have to do is to convince SQL Server to use these kinds of IFilters (Mr. Yogi, thank you for sharing this trick). For this, we will use some SQL script.

-- Check the settings
select serverproperty('IsFullTextInstalled')
GO

-- We will use the IFilters hosted by the Windows
EXEC sp_fulltext_service @action='load_os_resources', @value=1;
GO
EXEC sp_fulltext_service @action='verify_signature', @value=0;
GO

-- Update and restart the FTS
EXEC sp_fulltext_service 'Update_languages'
GO
EXEC sp_fulltext_service 'Restart_all_fdhosts'
GO

-- Checking loaded documents and languages
SELECT * FROM sys.fulltext_document_types order by document_type

SELECT * FROM sys.fulltext_languages ORDER BY lcid

Result

As they say, one picture is worth a thousand words, so I show you the result:

No more TF400644 notification

Wrap up

You can use different kinds of programming languages with the Azure DevOps Server for creating applications and services, and you can also use several types of human languages for storing information. The full-text search function can work with non-SQL Server related IFilter components, even if they are from a 3rd party provider.

Did I miss something or stated false information? Share your thoughts and let us learn!