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

~ 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/