SCOM12 R2: FINALLY The Collation Settings Issue Is Addressed!

The past
As we all know up to SCOM12 R2 the Collation Settings of the SCOM SQL databases was crucial and ONLY SQL_Latin1_General_CP1_CI_AS worked, even though Microsoft stated differently.

Main reason here was that the installer of SCOM installed the Data Warehouse database with this SQL Collation setting (SQL_Latin1_General_CP1_CI_AS) no matter what (SQL) Collation Setting the SQL instance itself was running.

And when there is a mismatch between the (SQL) Collation settings of both SCOM SQL databases (OperationsManager & Data Warehouse) you could end up in situations like these. Only a reinstall would solve that issue OR – when being blessed with really power SQL DBAs – the databases could be preserved while modifying the SQL Collation Settings.

The good news
Finally, with SCOM12 R2 this issue is fixed! The installer of SCOM 2012 R2 doesn’t install the Data Warehouse database anymore with the SQL Collation Settings SQL_Latin1_General_CP1_CI_AS, but uses the (SQL) Collation setting of the related SQL instance instead.

And – FINALLY – now SCOM 2012 R2 REALLY supports many different (SQL) Collation Settings, compared to the previous versions of SCOM 2012.

On top of it all, instead of using SQL Collation Settings (starting with the prefix SQL) Microsoft has decided to move on to supporting Windows Collation Settings. This is good as well since SQL Collation Settings will be phased out in the years to come and fully replaced by Windows Collations instead.

What Collation Settings are supported with SCOM 2012 R2?
Quite a lot actually. And no, I haven’t tested them all (most of them not actually, the ones I am 100% sure about are printed in bold) but this is what Microsoft TechNet states:

SQL Server collation settings for all databases must be one of the following:

1. SQL_Latin1_General_CP1_CI_AS;
2. Latin1_General_100_CI_AS, // EN, IT, DE, PT-BR, NE, PT-PT;
3. French_CI_AS; French_100_CI_AS;
4. Cyrillic_General_CI_AS;
5. Chinese_PRC_CI_AS;
6. Chinese_Simplified_Pinyin_100_CI_AS, // CN simplified;
7. Chinese_Traditional_Stroke_Count_100_CI_AS, // CN traditional, CN-HK;
8. Japanese_CI_AS;
9. Japanese_XJIS_100_CI_AS;
10. Traditional_Spanish_CI_AS;
11. Modern_Spanish_100_CI_AS;
12. Latin1_General_CI_AS;
13. Cyrillic_General_100_CI_AS, // RU;
14. Korean_100_CI_AS;
15. Czech_100_CI_AS;
16. Hungarian_100_CI_AS;
17. Polish_100_CI_AS;
18. Finnish_Swedish_100_CI_AS.

So this is really some good news which isn’t really specifically documented.

How does this work out for me?
Well, when you go for a CLEAN install of SCOM 2012 R2, the above mentioned (SQL) Collation Settings are the ones you can choose from.

But when you run an UPGRADE from a previous version to (finally) SCOM 2012 R2, you’ve deal with the (SQL) Collation Settings already in place in the previous version of SCOM.

And no, an upgrade WON’T change the (SQL) Collation Settings of any SCOM database NOR will it fix issues for you when you’re having issues with conflicting (SQL) Collation Settings.

In cases like these it’s far more better (and easier) to install a brand new SCOM 2012 R2 environment and run an along side migration scenario instead of an inplace upgrade scenario.