Share via

MOSS, BizTalk, SCOM and SQL Server Collations

  • Article

Hello all,

The topic today is database and server collations. As part of pre-installation tasks for SQL Server, we should always take into account what is the collation required by the user databases. Why is that? Collation is a subject many times overlooked when installing SQL Server, and many just use the default when installing SQL Server. The default is SQL_Latin1_General_CP1_CI_AS, which stands for English (United States) with dictionary order case insensitive and accent sensitive.

defaultcollation

But collations are very important and should not be overlooked, considering they control the code page used to store non-Unicode data and how SQL Server sorts and compares characters that are stored in non-Unicode data types.
Ultimately, it can have a significant impact with sorts, comparisons and JOINs. And these generate collation conflicts or inconsistent results that affect either databases in the same instance, or server-to-server transactions.
Apart from these issues, some products actually address collation support in the documentation, and only support certain configurations. That is the case of SharePoint Server, BizTalk and SCOM (System Center Operations Manager).
Failure to comply with these requirements can yield collation errors that may even require you to reinstall a product (see SQL collation "SQL_Latin1_General_CP1_CI_AS" needs to be designated for installing OpsMgr DB or Readme for BizTalk 2010) or change the SQL Server collation. For that you have to rebuild the system databases or reinstall the database engine. The procedure on how to rebuild the system databases in SQL Server 2008 should be thoroughly read, including how to alter the server’s collation.

The SCOM 2007 R2 documentation refers that “SQL collation for all databases must be SQL_Latin1_General_CP1_CI_AS; no other collation configurations are supported”. As for SCOM 2007, the documentation refers that “for Operations Manager Operations database, the SQL collation must be SQL_Latin1_General_CP1_AS, no other collation configurations are supported”. Bottom line, the SQL Server should be installed using the referred collation, depending on the product version.

SharePoint Server 2007 documentation only supports the use of “(…) Latin1_General_CI_AS_KS_WS collation because the Latin1_General_CI_AS_KS_WS collation most closely matches the Microsoft Windows NTFS file system collation” for the databases, but SQL Server may not have this collation. However, if you absolutely cannot have the database engine with that particular collation, the documentation also states that any any CI collation is supported for the system databases, but recommends using SQL_Latin1_General_CP1_CI_AS as the instance collation, since that’s where the product was tested on. This is all valid for SharePoint Server 2010.
Still, strongly consider having the SQL Server that supports SharePoint Server with the same collations as the databases, to avoid any issues. See how to troubleshoot collation conflict errors when executing queries that involve columns with different collations.

Finally, BizTalk documentation recommends using SQL_Latin1_General_CP1_CI_AS collation, and failure to do so can result in several issues. The documentation also explicitly refers that it does not support binary (case sensitive) collations.

So as far as collations go, there is much more than meets the eye here. If you didn’t already do so,  make a point to always know the collation pre-requisites for all your databases to avoid future issues.

Until next time!

References and further reading:

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.