WARNING! The fix implemented below will leave your data warehouse database in an unsupported state. This may lead to data corruption, and you may have to restore from a backup. Microsoft will not support such direct edits of the database, and I cannot be held responsible for any consequences. I recommend you open a case with Microsoft if you are experiencing these problems.
Following an internal upgrade from DPM 2012 R2 to 2016, I replaced the appropriate DPM management packs in SCOM, upgrading them to version 5.0.1300.0 (released on 26/10/2016), and monitoring works perfectly. However, there were persistent errors logged on the management servers, relating to the data warehouse:
Event ID: 31565
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: ’34f57e9b-2777-8c69-a5a6-fbc8d0f1824f’, Management Pack Version-dependent Id: ‘e3a96ce5-bfb2-07b8-ef66-4c711a6606c7’; Target: DataSet, Id: ‘579fd046-1d03-4e54-9c4e-b3534a06a704’. Batch ordinal: 8; Exception: Violation of UNIQUE KEY constraint ‘UN_StandardDatasetAggregationStorage_BaseTableName’. Cannot insert duplicate key in object ‘dbo.StandardDatasetAggregationStorage’. The duplicate key value is (tbl_DPM_BackupEvents).
The statement has been terminated.One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component
Instance name: Data Warehouse Synchronization Service
and
Event ID: 31565
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: ‘6d9addfa-7b4c-4536-9fcc-ad6af21f835c’, Management Pack Version-dependent Id: ‘9e315942-a99b-8bb9-56d9-712f601901e7’; Target: DataSet, Id: ‘e13dde51-363c-47b2-8133-f892de58d3d0’. Batch ordinal: 8; Exception: Failed to rename staging table as part of dataset maintenance. sp_rename returned ‘2627’
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component
Instance name: Data Warehouse Synchronization Service
Initially, as monitoring was working, I was content with leaving these data warehouse problems alone: we rarely access the data warehouse and have never done so for DPM, and I assume that Microsoft are working on a fix. However, we recently ran into some other data warehouse problems, so along the way I wanted to clean these up.
The problem was caused by old datasets being left in the data warehouse database by the DPM 2012 R2 management pack. This wouldn’t usually be a problem, but the same table names are used by the DPM 2016 MP, and this causes conflicts that prevented the new MP from being able to deploy itself. The solution was to remove the old DPM 2012 R2 datasets, which I no-longer needed, using a script on Mihai Sarbulescu’s blog. Although the script is described as being for SCOM 2007 R2, I ran it against SCOM 2016 without any difficulties (although I had to fix a small typo :p).
Evidence of the problem is the events mentioned above and output from the below query where there are zeroes in the InstallCompletedInd
column for DPM datasets. This indicates failed deployments for DPM datasets to the database.
USE OperationsManagerDW SELECT * FROM dbo.Dataset ORDER BY InstalledDateTime DESC
WARNING! Again, the below fix is unsupported by Microsoft, and I can’t be held responsible for this or any other consequences. You will also lose your old DPM data from the data warehouse database by executing the below.
The steps to my solve my problem were:
- Stop the healthservice, cshost and omsdk services on all management servers.
- Take a backup of the data warehouse database.
- Execute the script to remove the old DPM datasets (see below).
- Start the healthservice, cshost and omsdk services on all management servers.
- Observe that, over the next few minutes, the DPM 2016 data warehouse components are automatically deployed, by examining the
dbo.Dataset
table (see above).
The script to remove the old DPM datasets is below. Full credit goes to Mihai Sarbulescu. Just remember to update the @MGName
variable.
USE OperationsManagerDW DECLARE @DataSetLike NVARCHAR(255), @MGName NVARCHAR(255), @DataSetId UNIQUEIDENTIFIER, @MGid INT, @FetchStatus INT, @AgId INT SET @MGName = N'Your management group name' SET @DataSetLike = N'DPM' DECLARE Cur CURSOR LOCAL FOR SELECT DataSetID FROM DataSet WHERE DatasetDefaultName LIKE (@DataSetLike + '%') OPEN Cur FETCH NEXT FROM Cur INTO @DataSetId SET @FetchStatus = @@FETCH_STATUS SET @MGid = ( SELECT ManagementGroupRowId FROM ManagementGroup WHERE ManagementGroupDefaultName = @MGName ) WHILE (@FetchStatus = 0) BEGIN SELECT @AgId = min(StandardDatasetAggregationStorageRowId) FROM StandardDatasetAggregationStorage WHERE DatasetId = @DataSetId PRINT N'Agid = ' + Cast(@Agid AS VARCHAR) WHILE (@AgId IS NOT NULL) BEGIN DELETE FROM StandardDatasetAggregationStorageIndex WHERE StandardDatasetAggregationStorageRowId = @AgId PRINT N'Deleting from StandardDataSetAggregationStorageIndex: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM StandardDatasetAggregationStorage WHERE StandardDatasetAggregationStorageRowId = @AgId PRINT N'Deleting from StandardDataSetAggregationStorage: ' + Cast(@DataSetId AS NVARCHAR(50)) SELECT @AgId = min(StandardDatasetAggregationStorageRowId) FROM StandardDatasetAggregationStorage WHERE DatasetId = @DataSetId PRINT N'Agid = ' + Cast(@Agid AS VARCHAR) END DELETE FROM ManagementGroupDataset WHERE DatasetId = @DataSetId AND ManagementGroupRowId = @MGid PRINT N'Deleting from ManagementGroupDataset: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM StandardDataset WHERE DatasetId = @DataSetId PRINT N'Deleting from StandardDataSet: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM StandardDatasetAggregationStorage WHERE DatasetId = @DataSetId PRINT N'Deleting from StandardDataSetAggregationStorage: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM StandardDatasetAggregationHistory WHERE DatasetId = @DataSetId PRINT N'Deleting from StandardDataSetAggregationHistory: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM StandardDatasetAggregation WHERE DatasetId = @DataSetId PRINT N'Deleting from StandardDataSetAggregation: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM StandardDatasetTableMap WHERE DatasetId = @DataSetId PRINT N'Deleting from StandardDataSetTableMap: ' + Cast(@DataSetId AS NVARCHAR(50)) DELETE FROM Dataset WHERE DatasetId = @DataSetId PRINT N'Deleting from DataSet: ' + Cast(@DataSetId AS NVARCHAR(50)) FETCH NEXT FROM Cur INTO @DataSetId SET @FetchStatus = @@FETCH_STATUS END CLOSE Cur DEALLOCATE Cur
If you’ve implemented the above, your data warehouse database should populate with DPM data after a day or two; however, you probably won’t be able to view the report as it’ll show this error:
An item with the same key has already been added.
This occurs because the report selects the display name of DPM servers, and Microsoft have reused the class name of objects discovered by both the DPM 2012 R2 and 2016 management packs, so there’s essentially duplicate data in the data warehouse, and for some server names two entries are returned to the report. The fix is to either delete the redundant data, which gets a bit convoluted because of the foreign key references, or to simply rename the old objects. Again, the usual disclaimer at the beginning of this article applies: if you do this, your database will be unsupported.
DECLARE @DisplayNameSuffix NVARCHAR(MAX) = '(DPM 2012 R2)' UPDATE me SET me.DisplayName = me.DisplayName + ' ' + @DisplayNameSuffix FROM dbo.ManagedEntity me INNER JOIN dbo.vManagedEntityManagementGroup memg ON me.ManagedEntityRowId = memg.ManagedEntityRowId INNER JOIN dbo.vManagedEntityType met ON me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId INNER JOIN dbo.vManagementPack mp ON met.ManagementPackRowId = mp.ManagementPackRowId WHERE memg.ToDateTime IS NOT NULL AND met.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.DataProtectionManager.2011.Library.DPMServer' AND mp.ManagementPackSystemName = 'Microsoft.SystemCenter.DataProtectionManager.2012.Library' AND me.DisplayName NOT LIKE '%' + @DisplayNameSuffix + '%'