支持提示：升级到 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。连接到该数据仓库数据库，然后粘贴下列代码并运行代码以便纠正问题。
代码如下所示：
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 管理和安全部门