One of the things I run across and I think of a lot of people with me is having a SQL installation with the wrong collation for use with SCOM. Mostly this happens when I want to install a new SCOM at a customer site and ask for a Windows installation to start with and the local system admin has a lot of enthusiasm and installs SQL on the box with default settings. I also see this happening often in the forums and questions get asked.
Well it is very simple. The SQL instance (SQL installation) MUST have a specific collation set. If it does not things will go wrong. The database will get the right collation at installation of SCOM or running the database create wizard. Any trying to change the collation afterwards will NOT work. A default install with next next next will not have the right collation set and you best just uninstall SQL and start over.
The only right collation you have to set when installing SQL is:
SQL_Latin1_General_CP1_CI_AS
Make sure you always use this one.
If you do not do this you will run into issues. For instance getting this error:
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.
This is discussed in a KB article at http://support.microsoft.com/kb/958979.
Always remember to use the correct collation and if somebody else has installed the SQL for you please make this one of the first checks to do.
