Collation conflicts in TFS 11 beta.

Over the years we have consolidated several Team Project Collections into one database instance. The collation on the collection databases varies, which was not a problem in TFS 2010. When we migrated to TFS 11 Beta, we ran into problems when we tried to associate artifacts to work items.

In Microsoft Test Manager we got “The Bug could not be saved, cannot resolve the collation conflict between….”

and this descriptive message box (“There is a problem on the server”) when we tried to link attachment to work items.

and the associate work item to changeset Checkin’ policy started to give us messages such as

“Changeset NN successfully checked in.

Failed to update the following work item:
ID XX. Reason: There is a problem on the server.

Contact your Team Foundation Server administrator.”

If you tried to manually associate changset to work items  we got:

The MTM error is obviously related to collation issues, but to pinpoint the cause for the other two, we ran a SQL trace and we got loads of MS SQL Error 468, wich are collation erors.

We saw that the team project collection databases had a different collation than the database instance.  There are several ways of resolving collation issues, but the recommendation I got from an SQL expert is not to try to change the collation directly, but to reinstall the database instances with the correct collation.

This is how we did it:

1.       Stop/Detach the team project collection databases

2.       Take backup of TP collection databasess

3.       Reinstall database instance(s) with the correct collation.

4.       Restore each TP collection into a database with a matching collation

5.       Attach the team project collection databases.

and that fixed the problem.

Since we hade 4 different collation types we had to install one database instance for each collation!!! Now we are running 4 database instances...