Saturday, 1 September 2012

System Center 2012 Operations Manager - Beware of SQL Server Collation!

One thing to beware of when installing System Center 2012 Operations Manager is that the SQL Server you install SCOM 2012 OperationsManager database onto, must have the SQL Server Collation set to SQL_Latin1_General_CP1_CI_AS.

An Illustration:

In this example we have an SQL Server 2008 R2, installed pretty much using defaults, and notice that - using a typical install - the Server Collation is Latin1_General_CI_AS:

Figure 1: SQL Server Management Studio > Server Properties > General > Server Collation

Then we install SCOM 2012 onto this SQL Server.
Notice that the OperationsManager database is created as Latin1_General_CI_AS:

Figure 2: SQL Server Management Studio > Databases > OperationsManager Properties > Options

But notice that the SCOM 2012 installer has created the OperationsManagerDW database (Data Warehouse) as SQL_Latin1_General_CP1_CI_AS:

Figure 3: SQL Server Management Studio > Databases > OperationsManagerDW Properties > Options

This is unsupported as the OperationsManager and OperationsManagerDW databases need to be on the same collation; and - alas - the only way to go back from this point is reinstall SQL and OpsMgr with the correct SQL collation.

Note 1: Using the drop-down menu to select the SQL_Latin1_General_CP1_CI_AS collation for the OperationsManager database, will not work.
Note 2: Using scripts to change the collation of the OperationsManager database is very unlikely to work (please comment if you have had success.)
Note 3: It is likely that this configuration error will go unnoticed (as it will work okay with the Collation mismatch) until someone comes along and installs the Veeam MP for SCOM, at which point the Veeam MP exposes the configuration error (it is not a problem with the Veeam MP) and you’ll see an error like the below in SCOM.

Figure 4: Data Warehouse failed to deploy database component

Further reading:

1 comment:

  1. Helpful blog, Operations management is of central importance to any organization, whether they are manufacturing companies producing physical products or organizations offering services, or whether the organization is in the private, public or voluntary sectors.