Brian McDermott (Microsoft CSS) a publié un billet sur le blog de l’équipe SCOM concernant un problème survenant après la mise à jour vers System Center 2012 R2 Operations Manager. En effet, le Data Warehouse peut subir des échecs de synchronisation. En observant le fichier de journalisation, vous observez :
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
La raison provient de la mise à jour des tables Alert avec de nouvelles colonnes pour mieux gérer l’intégration de TFS. Malheureusement dans certaines situations, le code qui ajoute ces colonnes au Data Warehouse ne s’exécute pas avec succès. Si vous observez l’évènement ci-dessus en voyant bien l’élément : Message: Invalid column name 'TfsWorkItemId' alors vous pouvez exécuter une requête SQL de rémédiation.
Avant toute opération, veillez à sauvegarder la base de données opérationnelle et le Data Warehouse du produit.
Exécutez ensuite :
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
Plus d’informations sur : http://blogs.technet.com/b/momteam/archive/2014/02/18/support-tip-upgrade-to-operations-manager-2012-r2-may-result-in-data-warehouse-synchronization-failures.aspx
