Team Foundation Server and SQL Collations

I just got through a grueling session of installing the release candidate for Team Foundation Server. And by grueling, I mean about 16 hours worth. And given all of the glowing reports, I was surprised that this install turned about to be more challenging that the Beta 3 Refresh.

My problem centered around one particular point in the process. After having installed IIS, SQL Server, and Sharepoint Services on a fresh version of Windows 2003 (and the .NET 2.0 Hotfix), the installation of TFS performs a health check. My results coming out of the health check was that the running SQL Server instance was using an unsupported collation. Now while I have installed SQL Server many times, I’ve never given much thought to the collation. So I started looking at the problem. Since I had made no changes during SQL Server installation (with respect to collation), I uninstalled SQL Server and reinstalled it specifically choosing the Latin1_General option. Also, because the instructions in the installation help file said that TFS didn’t support case sensitive, binary or binary 2 collations, I left all of the check boxes on that form blank. To no avail. The SQL Server collation was not supportable by TFS

Just to be clear, after all of the dust had settled, my SQL Server’s collation was Latin1_General_CI_AI and TFS just didn’t like that.

So I go digging into the installation log file and come across the SQL statement which gets executed as part of the collation health check:

SELECT * FROM (SELECT CONVERT(char(100), SERVERPROPERTY('collation')) AS Collation) as c WHERE (Collation LIKE '%[_]CS%') OR (Collation LIKE '%[_]AI%') OR (Collation LIKE '%[_]BIN%') OR (Collation LIKE '%[_]BIN2%')

The thing that stood out for me was the inclusion of the Collation Like ‘%[_]AI%’ clause. Unless I’m reading this wrong, it means that TFS doesn’t like Accent Insensitive collations. And I don’t see this small tidbit documented anyplace.

The sole bright spot is that after I reinstalled SQL Server with the ‘Accent – sensitive’ check box on the Collation Settings portion of the installation checked, all went well. I now have a running version of TFS that is capable of working over HTTP. Let the games begin.