SCOM: Failed to deploy Data Warehouse Component
During a recent health check at a customer site I found that the RMS in a SCOM2007 install was in critical state. During my health check this is basically the first thing I check because in SCOM2007 this is still the heart of your environment.
Check of the health explorer revealed the following rather odd message:
The Data Warehouse Database Component Deployment Recovery State – (Data Warehouse Synchronization Server) is in critical error.
First step of my troubleshoot is to check the event log of the RMS server for errors indicating this issue.
The following message was reported which caused this monitor to go to critical state:
Full message:
Event ID: 31565
Description:
Failed to deploy Data Warehouse component. The operation will be retried.Exception ‘DeploymentException’: Failed to perform Data Warehouse component deployment operation: Install; Component: Script, Id: ‘3a49a530-26a2-c525-35fe-69df5898f150’, Management Pack Version-dependent Id: ‘c9942f8c-3e77-e09d-1864-16db528bc3d2’; Target: Database, Server name: “Dbaseserver”, Database name: ‘OperationsManagerDW’. Batch ordinal: 1; Exception: Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near ‘,’.
Incorrect syntax near ‘,’.
Incorrect syntax near ‘,’.
Incorrect syntax near ‘,’.
Incorrect syntax near the keyword ‘ELSE’.
After troubleshooting the issue once more I found out that it happened right after I have implemented the new version of Windows Server Operating System Management Pack (6.0.6972.0) more specific the “Windows Server Operating System Reports” Management Pack which is a part of the management pack.
After carefully investigating I found in the Management pack guide (which can be downloaded here) following statement in the previous version which was not installed yet on the environment (page 8):
“The Microsoft.Windows.Server.Reports Management Pack will only import on systems that use SQL Server 2008 as the Database and is not currently supported on SQL Server 2005.”
As the customer is still on SQL 2005 dbase this was the cause. After removing the management pack from the environment the error disappeared and the RMS came back healthy again.
Lesson Learned here: Always check the management pack history fully and not just the latest changes.