Failed to store data in the Data Warehouse. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Phew. Had my piece of action today! At a customers site this error in the OpsMgr event log of the RMS popped up. And not just one time:

No way! Many times. Too many times. So time for action. But never ever thought I would ending up INSTALLING SQL SERVER 2008 for SCOM R2 AGAIN!

However, this is what I found:

See the red high lighted part. Say WHAT?

I ran through all the official guides from Microsoft about SCOM R2, but only one online document mentions this (special thanks to Oskar Landman):

However, the events are really serious and need attention. So time to dive deeper. Lets take a look at the OpsMgr database itself. As you can see the collation is correct here:

(Picture taken from one of mine test environments, out of respect of the customers privacy. However it reflects the situation I bumped into.)

Lets check out the SQL Server – which is hosting this database – and keep our fingers crossed:

(Picture taken from one of mine test environments, out of respect of the customers privacy. However it reflects the situation I bumped into.)

OUCH!

But… WHY do I see this error now? Never ever seen this error before. And believe me, I have installed many SCOM (R2) Management Groups. So it was time to call a real SQL Guru. Met him yesterday but he KNOWS his stuff about SQL, the SQL Engine in particular. So I told him what I bumped into and how to go about it.

His advice was to reinstall SQL Server totally from scratch since changing the Collation of the SQL Server isn't something to be taken lightly. And yes, I know my way around in SQL but also know when to listen to the real guru’s. So time for action!

This is what I did:

1. Make backups of the SCOM R2 Databases (OperationsManager & OperationsManagerDW) through the SQL Server Management Studio;
2. Close down the RMS;
3. Close down the MS servers;
4. Remove SQL;
5. Remove SCOM R2 Reporting;
6. Remove SCOM R2 Agent from the SQL Server (just to be sure all SCOM registry entries are gone);
7. Reboot the server;
8. Reinstall SQL Server 2008 SP1 (slipstreamed) and choose the right collation for SCOM (SQL_Latin1_General_CP1_CI_AS):
   
9. Reinstall CU# 7 for SQL Server 2008 SP1;
10. Run this script against the Master database;
11. Restore the OperationsManager and the OperationsManagerDW databases;
12. RMS only (!!!); start the related SCOM R2 services;
13. On the SQL Server: install SCOM R2 Reporting (DESELECT the DATA WAREHOUSE database!!!).
    - First time run will not succeed and the Reporting Wunderbar will disappear from the SCOM R2 Console, which is GOOD.
    - Follow Graham Davies advice on table of the Data Warehouse, found here.
    - Restore the SQL Server Reporting Services instance with the tool ResetSRS.exe. Procedure to be found here.
    - Reinstall SCOM Reporting , (DESELECT the DATA WAREHOUSE database!!!) now it will run just fine.
14. Now all is well again and SCOM R2 will operate just fine.

Of course, the SCOM R2 Agent needs to be reinstalled, the owner of the SCOM R2 databases need to be set correctly, some new backup plans of the SCOM R2 databases (don’t forget the MASTER database!) need to be created, but those are the details. Also the remaining SCOM R2 Management Servers need to be started again (Health Service).

Above mentioned steps worked for me and the earlier mentioned error is GONE! Great!

Lets run a final check of the SQL Server:

Way much better! Phew!

Lesson Learned:
When installing SQL Server for SCOM (R2) databases, select SQL_Latin1_General_CP1_CI_AS as collation. Otherwise one might bump into this situation.