SQL Server Collation Requirements for Team Foundation Server

  • Article

When you install SQL Server, you must consider two things about collation settings that could affect your Visual Studio Team Foundation Server deployment:

  • Requirements for Team Foundation Server 

  • All instances of SQL Server in your Team Foundation Server deployment must use the same settings

You can set collation settings for the Database Engine and SQL Server Analysis Services. Collation settings include character set, sort order, and other locale-specific settings, which are fundamental to the structure and function of SQL Server databases. You cannot change these settings after installation.

Requirements for Team Foundation Server

To work with Team Foundation Server, the collation settings for SQL Server must be case insensitive, accent sensitive, and not binary. If multiple SQL Server are running an instance of Database Engine or SQL Server Analysis Services for Team Foundation Server, the collation settings that you use must be the same across all these servers.

SQL Server bases your default collation settings on the locale of your operating system. The default setting for U.S. English and most other locales often meets the requirements for Team Foundation Server. However, those settings might not support all of the data that your organization must store in Team Foundation Server. In that case, you must find a setting that supports your data and is accent sensitive, not case sensitive, and not binary.

If you install Database Engine Services or Analysis Services for SQL Server 2008, you can change collation settings on the Server Configuration page, by clicking the Collation tab and then clicking Customize. You may want to specify an option under Collation designator and sort order. For example, you can specify Latin1_General (and select the AS checkbox), if you require support for additional characters.

For most other locales, the default setting is an option under Collation designator and sort order. Make sure that the settings match the requirements for Team Foundation Server. If you must change this setting, you should specify the option that is named for your locale with "_100" after it, where possible. For example, you can use Japanese_100 collation if you use Unicode CJK Extension A characters or Unicode Surrogates in the following ways:

  • Names of objects, such as queries or projects, in Team Foundation Server.

  • Files or paths that are checked into the version control system.

  • Any field of the work items that is used for searches.

To avoid problems with double-width or hiragana/katakana-equivalent characters, you should select the check boxes to enable Kana and width sensitivity when you install SQL Server.

For more information, see the Microsoft Web site: Collation Settings in Setup.

See Also

Reference

Checklist: Single-Server Installation

Checklist: Multiple-Server Installation

Checklist: Upgrade Team Foundation Server By Using More Than One Server

Concepts

How to: Manually Install SQL Server 2008 for Team Foundation Server