SCOM: Upgrade to Operations Manager 2012 R2 may result in Data Warehouse synchronization failures

Leave a reply

Brian McDermott highlighted an issue to watch out for when upgrading to SCOM 2012 R2 where you may get Data Warehouse synchronization failure errors after the upgrade.

The article can be found here with solid reasoning as to the cause and solution:

Please note that the Event ID 31565 noted above is a very generic error and you should only run the SQL below if the description identifies that it is the problem with the TFSWorkItemID column.

Error below:

Log Name:      Operations Manager
Source:        Health Service Modules
Date:
Event ID:      31565
Task Category: Data Warehouse
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      OMMS.domain.com
Description:
Failed to deploy Data Warehouse component. The operation will be retried.
Exception ‘DeploymentException’: Failed to perform Data Warehouse component deployment operation: Install; Component: DataSet, Id: ‘0d698dff-9b7e-24d1-8a74-4657b86a59f8′, Management Pack Version-dependent Id: ’29a3dd22-8645-bae5-e255-9b56bf0b12a8′; Target: DataSet, Id: ’23ee52b1-51fb-469b-ab18-e6b4be37ab35’. Batch ordinal: 3; Exception: Sql execution failed. Error 207, Level 16, State 1, Procedure vAlertDetail, Line 18, Message: Invalid column name ‘TfsWorkItemId’.

This issue can be fixed with the below SQL query, as always BACKUP your databases and proceed at your own risk:
USE OperationsManagerDW
 
DECLARE @GuidString NVARCHAR(50)
SELECT @GuidString = DatasetId FROM StandardDataset
WHERE SchemaName = ‘Alert’
 
— update all tables that were already created
DECLARE
   @StandardDatasetTableMapRowId int
  ,@Statement nvarchar(max)
  ,@SchemaName sysname
  ,@TableNameSuffix sysname
  ,@BaseTableName sysname
  ,@FullTableName sysname
 
SET @StandardDatasetTableMapRowId = 0
 
WHILE EXISTS (SELECT *
              FROM StandardDatasetTableMap tm
              WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
                AND (tm.DatasetId = @GuidString)
             )
BEGIN
  SELECT TOP 1
     @StandardDatasetTableMapRowId = tm.StandardDatasetTableMapRowId
    ,@SchemaName = sd.SchemaName
    ,@TableNameSuffix = tm.TableNameSuffix
    ,@BaseTableName = sdas.BaseTableName
  FROM StandardDatasetTableMap tm
          JOIN StandardDataset sd ON (tm.DatasetId = sd.DatasetId)
          JOIN StandardDatasetAggregationStorage sdas ON (sdas.DatasetId = tm.DatasetId) AND (sdas.AggregationTypeId = tm.AggregationTypeId)
  WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
    AND (tm.DatasetId = @GUIDString)
    AND (sdas.TableTag = ‘detail’)
    AND (sdas.DependentTableInd = 1)
  ORDER BY tm.StandardDatasetTableMapRowId
 
  SET @FullTableName = @BaseTableName + ‘_’ + @TableNameSuffix
 
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @FullTableName AND TABLE_SCHEMA = @SchemaName
    AND COLUMN_NAME = N’TfsWorkItemId’)
  BEGIN
    SET @Statement = ‘ALTER TABLE ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@FullTableName) + ‘ ADD TfsWorkItemId nvarchar(256) NULL’
    EXECUTE (@Statement)
  END
 
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @FullTableName AND TABLE_SCHEMA = @SchemaName
    AND COLUMN_NAME = N’TfsWorkItemOwner’)
  BEGIN
    SET @Statement = ‘ALTER TABLE ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@FullTableName) + ‘ ADD TfsWorkItemOwner nvarchar(256) NULL’
    EXECUTE (@Statement)
  END
END
 
— alter cover views
EXEC StandardDatasetBuildCoverView@GUIDString, 0
GO

 

Loading

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.