Thursday, April 29, 2010

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:
image

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:
image

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):
image

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:
image
(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:
image
(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):
    image
  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:
image

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.

14 comments:

OskarL said...

Hi Marnix,

Have run into this one myself. As far as I can recall the prereq gave a warning on the collation settings prior to installation.
During my investigation I run into a part from the Quick Start guide which actually mentions this.It is under minimum software requirements.
http://technet.microsoft.com/en-us/library/bb309428.aspx


Great you finally solved it although it was a long way ;-)

Cheers.
Oskar Landman

Marnix Wolf said...

Hi Oskar.

Thanks for your comment. I updated my blogposting accordingly.

Cheers,
Marnix

Marnix Wolf said...

Hi Oskar.

The PreReq Tool did not gave any clue what so ever...

Cheers,
Marnix

OskarL said...

Hmmm that's a death end then ;-)
If I have time the upcomming days maybe I can test this in a test environment. Will let you know...

Unknown said...

Marnix,

Do you think this only applies to SQL 2008 (and not 2005)?

I am about to upgrade SQL (2005 -> 2008) in a SCOM 2007 R2 installation and found your post. Went back and checked my site and found that the existing SQL 2005 *server* has "Danish_Norwegian_CI_AS" but the *databases* have "SQL_Latin1_General_CP1_CI_AS" as they should. But everything has been running smoothly. Somewhat scared I went back and checked the event logs and found some 31552 events posted during the SCOM SP1 -> R2 upgrade, but they stopped sometime during the process and haven't been seen since (sorry, I can't pinpoint exactly what step in the upgrade caused the events to stop).

So now I am worried that once I upgrade SQL to 2008 I will start seeing them again. Any thoughts?

- Kim

Unknown said...

....oh, by the way, in your #9 above, I think you mean CU# 3, not CU# 7.

- Kim

Marnix Wolf said...

Hi Kim,

No. It is not a typo. I really mean CU#7 for SQL Server 2008 SP1: http://thoughtsonopsmgr.blogspot.com/2010/04/cumulative-update-7-for-sql-server-2008.html

Cheers,
Marnix

Marnix Wolf said...

Hi Kim.

Thanks for visiting my blog. The collation setting also applies to SQL Server 2005. Taken from the mentioned webpage in this article: 'SQL collation must be SQL_Latin1_General_CP1_CI_AS; no other collation configurations are supported
'

So where it comes down to, is that when you experience any issue and you contact Microsoft PS and they find out the collation to be 'out-of-order', you may enter 'the realm of unsupported configurations'.

Cheers,
Marnix

Marnix Wolf said...

Hi Moe.

In the posting I write what to do about the SQL server. So the Agent only needs to be reinstalled on the SQL server.

Would be a bit of a drama when ALL the Agents needed a reinstall.

Cheers,
Marnix

srgbrt said...

Hi Marnix,

I just ran into the same issue after installing the MP for Exchange 2010 (never had this before with any other MP!).

I've been digging a bit and it's related to the Windows Locale of the server when you install SQL on the box.
See 'Default Collations in SQL Server Setup' at http://msdn.microsoft.com/en-us/library/ms143508.aspx.

Only a Windows Locale of English US will default to SQL_Latin1_General_CP1_CI_AS. Lots of other locales Dutch, English UK, etc all default to Latin1_General_CI_AS for the Server Collation.
So in my case the Server Collation is Latin1_General_CI_AS, the Database collation of the OperationsManager and OperationsManagerDW are SQL_Latin1_General_CP1_CI_AS.

I think MSFT should be aware that most countries do not install their servers with the English US locale ... and this is not the first time we run into "localization issues" with OpsMgr.

Cheers,
Serge

srgbrt said...

Hi Marnix,

I just ran into the same issue after installing the MP for Exchange 2010 (never had this before with any other MP!).

I've been digging a bit and it's related to the Windows Locale of the server when you install SQL on the box.
See 'Default Collations in SQL Server Setup' at http://msdn.microsoft.com/en-us/library/ms143508.aspx.

Only a Windows Locale of English US will default to SQL_Latin1_General_CP1_CI_AS. Lots of other locales Dutch, English UK, etc all default to Latin1_General_CI_AS for the Server Collation.
So in my case the Server Collation is Latin1_General_CI_AS, the Database collation of the OperationsManager and OperationsManagerDW are SQL_Latin1_General_CP1_CI_AS.

I think MSFT should be aware that most countries do not install their servers with the English US locale ... and this is not the first time we run into "localization issues" with OpsMgr.

Cheers,
Serge

Faizel said...

Hi Marnix,

i have run into this problem myself, i did follow these steps and i followed the steps except step 5 removing reporting server.

"as i thought that it isn't needed"
what i did is the following.

after reinstalling SQL i recreated the Services Account logins. and started with the restores.

it is a well written article many thanks for all your time and effort putting in to SCOM Community.

Cheers
Faizel

Dillthedog said...

Just testing out SCOM and SCCM 2012, found the porblem with SCCM, when i looked at SCOM I found the problem also, this is now fixed, thank you for this post, great, nice one...........

Dave.......

vildauget said...

Thank you for an easy step-by-step guide to fix the collation issue.

Used the guide just now, and here are my comments from that experience, for any future operations:

As reporting server was on a dedicated server, we tried the operation without reinstalling reporting. It worked fine. We just made sure to backup and restore the reportserver DB as well.

SCOM had problems logging into the databases after the restore. We found the SCOM users in the DB, but they were not present under "SQL server\security\logins". As soon as we tried to create those users and give them DB rights, we got an error that they already existed in the DBs, and they magically popped up under \logins as well. Login worked fine then.

We also used this Technet guide on "how to move the OperationsManager DB" ,to "set ENABLE_BROKER", http://technet.microsoft.com/en-us/library/cc540384.aspx.

Also, event 33333's from DataAccessLayer were spammed on the OperationsManager event log on SCOM RMS, so we used the following easy guide from Aman Dhally to stop the spam, http://opsmgradmin.blogspot.com/2011/05/scom-event-id-33333-in-scom-from-source.html .

Finally, had to start my console with the /clearcache option to get rid of an active alert that had dissapeared during the operation, and thus being lost and scared, not able to move or say goodbye. Kevin Holman has a note on that here, http://blogs.technet.com/b/kevinholman/archive/2007/12/19/tired-of-your-console-throwing-errors-or-manually-clearing-your-console-cache.aspx

Thanks again, went smooth!