SQL Server Collation settings for System Center Service Manager 2012

There is a lot of conflicting or confusing information surrounding the required collation settings for SQL Server, as it pertains to hosting the SCSM 2012 database and associated data warehouse databases.

During installation, this error appears if you leave the collation at the default, which is SQL_Latin1_CP1_CI_AS.

This TechNet article says:

For this release, make sure that you use the same collation in SQL Server and Analysis Services on the computers that host the Service Manager database, the data warehouse database, analysis services database, and the Reporting Services database. For more information about SQL Server collations, see Using SQL Server Collations.

If your SQL Server installation is using the default collation (SQL_Latin1_General_CP1_CI_AS), a warning message appears, stating that the collation is not one of the supported collations for Service Manager and that an unsupported collation can cause unpredictable behavior in multilingual environments.

Caution
Support for multiple languages in Service Manager is not possible when you are using the default collation (SQL_Latin1_General_CP1_CI_AS). If later you decide to support multiple languages using a different collation, you have to reinstall SQL Server. There are no issues with using the default collation with the English-only installations of Service Manager. For more information about language support, see Language Support for System Center 2012 - Service Manager.

Following that link lists SQL_Latin1_General_CP1_CI_AS as a supported collation! This is where my brain starts exploding.

There are also numerous references that state that the Analysis Service, Reporting Service, and DBE all need the same collation set, however SQL_Latin1_General_CP1_CI_AS is not an option for either. There are Latin1, case insensitive, accent sensitive choices available, but not SQL_Latin1_General_CP1_CI_AS specifically.

To rub salt in the wounds, the Service Managers docs say that in order to pull SCOM data into the SCSM data warehouse, the collations much match. However, the SCOM 2012 SP1 docs state :

SQL Server collation settings for all databases must be one of the following: SQL_Latin1_General_CP1_CI_AS, French_CI_AS, Cyrillic_General_CI_AS, Chinese_PRC_CI_AS, Japanese_CI_AS, Traditional_Spanish_CI_AS, or Latin1_General_CI_AS. No other collation settings are supported.

So, it seems that in order to pull SCOM data into SCSM, you're forced to use an unsupported collation that limits you to a single language. Is that right? If so, why is SQL_Latin1_General_CP1_CI_AS listed as a supported and compatible collation and why does the installer complain about it?

Did I die in my sleep and wake up in hell?

I finally received an answer directly from a very smart gentleman at Microsoft. I'll share the contents of our findings that don't have identifiable customer information in them:

• The OpsManager database and SM DWStagingAndConfig database must have matching collations locales not necessarily the same exact collation as implied by some of the documentation.

• All other databases in OM and SM can have mismatched collations (and locales) as long as the individual component supports it.

• SCOM data warehouse always uses SQL_Latin1_General_CP1_CI_AS regardless of the SQL Server collation. This is a product issue, so only install SCOM on a SQL Server instance with a default collation of SQL_Latin1_General_CP1_CI_AS

This means the following in a situation where all SCOM databases are on the same server and all SCSM databases are on the same server:

• SCOM should be installed on a SQL Server instance with a default collation of SQL_Latin1_General_CP1_CI_AS

• Ops Manager and Service manager should ideally be installed on a server(s) with matching collations, but there is a little variance allowed as shown below (note this is not published anywhere AFAIK). Note that to do matching collations, you would need to use SQL_Latin1_General_CP1_CI_AS, which locks you into English-only.

• If you are going to be co-locating SM and OM on the same server, just be sure during SQL installation you select one of the 3 collations on the left.

• If you are installing SM and OM on different SQL Server instances be sure that the collations are supported (note: by default SQL installs with a collation of SQL_Latin1_General_CP1_CI_AS on an US English OS).

The engineer from Microsoft recommended that Service Manager be installed on a server with Latin1_General_100_CI_AS and that SCOM be installed on a server with SQL_Latin1_General_CP1_CI_AS due to the SCOM data warehouse installer bug.

It looks like this is yet another case of Microsoft documentation being out-of-date and inaccurate. Excerpting from Clarification on SQL Server Collation Requirements for System Center 2012 from the System Center MSDN blog:

There has been quite a bit of confusion over the requirements for SQL Server collation for the various components of System Center. We didn’t make a coordinated effort here to make this as easy as it could and should be. The documentation has conflicts within itself in some cases.

(I certainly don't mean to bash on Microsoft-- but anybody who has used their products for any length of time knows that there's typically a lag between the documentation and new product releases. Rightly or wrongly, they seem to have a culture of shipping bits before shipping words...)

The gist of this article is that there is a conflict in the documentation between SCSM and SCOM. The article goes on to describe collations that have been tested as being compatible (the matrix near the bottom of the article).