支持提示:升级到 Operations Manager 2012 R2 可能会导致数据仓库同步失败

~ Brian McDermott

大家好,Brian McDermott 将为您提供另外一项 Operations Manager 支持提示。如果您已经从 System Center 2012 Operations Manager Service Pack 1 (OpsMgr 2012 SP1) 升级到 System Center 2012 R2 Operations Manager (OpsMgr 2012 R2),可能会出现以下错误:

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

发生这种错误的原因在于,我们已经更新警报表并新增了几列,以便更加有效地实现 TFS 集成,但遗憾的是,在某些情况下,用于向数据仓库表添加这些列的升级代码无法成功完成操作。

如果您在登录其中一个管理服务器时看到上述错误,尤其是描述中包含以下句子:Message: Invalid column name 'TfsWorkItemId' ;那么,应当运行下方的 SQL 语句,将这些列添加到缺少相应列的表中。

首先,由于我们希望直接编辑数据库,因此首先获取两个 Operations Manager 数据库的备份是绝对必要的。没错,就是 OperationsManager DB 和 OperationsManagerDW DB。有时数据库恢复可能同时需要用到这两个数据库,因此在进行任何重要更改之前,始终需要对两者进行备份。

另外请注意,上方指出的事件 ID 31565 是一类十分常见的错误,如果描述指出 TFSWorkItemID 列存在问题,则只需运行下方的 SQL 语句。如果存在对 31565 事件的另一种描述,并且您需要进一步的协助来排除这个故障,亦或如果遇到过这个问题,但不确定如何执行恢复步骤,那么请通过微软支持人员提出申诉,我们将很乐意为您提供协助。

恢复步骤

在托管 Operations Manager 数据仓库数据库的 SQL Server 上,打开 SQL Server Management Studio。连接到该数据仓库数据库,然后粘贴下列代码并运行代码以便纠正问题。

 clip_image002

代码如下所示:

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

希望对您有所帮助!

Brian McDermott | 专家级工程师 | Microsoft CTS 管理和安全部门