Team Foundation Server and SQL Server Collation

One problem, we are having sometimes during Team Foundation Server setup, caused by the fact that TFS has serious limitations on SQL server collation. To be exact, following collation are not supported:

• Accent Insensitive
• Case sensitive
• Binary
• Binary2

In case, we have to install TFS on top of existing SQL Server, there is a good chance, that System Health Check report, prepared by TFS setup, will contain "SQL Server collation is not set to one supported by Team Foundation Server" error. What can we do now? Before we can proceed with the setup, we have to change MS-SQL collation to one of supported by TFS. To change collation after SQL Server installation we have to rebuild master database. The easiest way to do it, is to insert SQL Server installation disk, switch to the CD drive and to run following command from command prompt:

start /wait <CD or DVD Drive>setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<SAPASSWORD> SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Following points should be taken in consideration:

• INSTANCENAME=MSSQLSERVER used to specify default instance of SQL Server. Use instance name to alter named instance (usually irrelevant in case of TFS setup)
• Replace <SAPASSWORD> by actual administrator password
• Select one of supported collations. Those that have suffix _CI_AS (Case Insensitive, Accent Sensitive), usually will be fine
• Rebuilding master database reverts all system database to their original state. This means you have to recreate all user databases and reapply all servicepacks that are not included with installation media