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

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

  • Comments 3
  • Likes

~ Brian McDermott

ToolsHi everyone, Brian McDermott here with another Operations Manager support tip for you. If you have upgraded to System Center 2012 R2 Operations Manager (OpsMgr 2012 R2) from System Center 2012 Operations Manager Service Pack 1 (OpsMgr 2012 SP1) you may receive the following error:

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'.

One or more workflows were affected by this. 

Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component
Instance name: 05910f88-cefb-4471-bd86-c6ddee871293
Instance ID: {750D388B-0CF2-358A-02B0-E528CF813478}
Management group: OMMG

The reason this is happening is that we have updated the Alert tables to add a couple of columns to better enable TFS integration, and unfortunately in some situations the upgrade code that adds these columns to the Data Warehouse tables is failing to complete successfully.

So if you are seeing the errors above when logged on one of your management servers, which, VERY SPECIFICALLY has the following in the description: Message: Invalid column name 'TfsWorkItemId', then you should run the SQL statement below to add those columns to the tables that are missing them.

First, since we are directly editing the database it is absolutely essential that we begin by taking backups of both of your Operations Manager databases. Yes, both the OperationsManager DB and the OperationsManagerDW DB. Recovery of the databases can sometime require both of them so prior to any important change always backup both of them.

Also 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. If there is a different description for the 31565 event and you need further assistance in troubleshooting it, or also if you have encountered this problem but are uncertain on how to follow the recovery steps, then please open a case with Microsoft Support and we will be glad to assist.

The Recovery Steps

On your SQL server hosting the Operations Manager Data Warehouse DB, open up SQL Server Management Studio. Connect to the Data Warehouse DB, then paste in the following code and run it to correct the problem.

clip_image002

The code is listed here:

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

Hope this helps!

Brian McDermott | Escalation Engineer | Microsoft CTS Management and Security Division

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up: http://blogs.technet.com/b/systemcenter/
System Center – Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/
System Center – Data Protection Manager Team blog: http://blogs.technet.com/dpm/
System Center – Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
System Center – Operations Manager Team blog: http://blogs.technet.com/momteam/
System Center – Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center – Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm

Windows Intune: http://blogs.technet.com/b/windowsintune/
WSUS Support Team blog: http://blogs.technet.com/sus/
The AD RMS blog: http://blogs.technet.com/b/rmssupp/

App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv

The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Do this apply when uphrading from scom 2007 r2 to scom 2012? Have seen simial errors after relocating operationsmanager db

  • Hi Avdo, this error and resolution is very specifically for the upgrade to OpsMgr 2012 R2 from OpsMgr 2012, as that is when we add the TFSWorkitemID. The Event ID 31565 is a very generic event so if you see it in other situations you need to refer to the Event details to understand the cause.