~ Brian McDermott
How did we get into this mess?
So some change in your environment resulted in problems so severe for Operations Manager that you decide it is time to reach for the backups and to restore the OpsMgr DB to before the fateful day began. Your OperationsManager DB is not too big, only 30GB in size, and you have a backup from last weekend so you quickly get things back to how it was by restoring the DB. You don’t bother to restore the OperationsManagerDW DB. After all it is “only” used for reporting, and you may want to keep that data, plus it is enormous. It’s more than 2TB in size and restoring that would take ages, so you decide to leave it alone.
Your swift action has restored the status quo, your alerts and notification are coming in again and all is good once more, or so you initially think….
So what exactly is the mess?
You start to notice events like the ones below cropping up in the Operations Manager event log on one of your Management Servers.
Log Name: Operations Manager
Source: Health Service Modules
Date:
Event ID: 31552
Task Category: Data Warehouse
Level: Error
Computer: OMMS.domain.com
Description:
Failed to store data in the Data Warehouse.
Exception 'SqlException': Sql execution failed. Error 2627, Level 14, State 1, Procedure ManagementPackInstall, Line 2879, Message: Violation of UNIQUE KEY constraint 'UN_ManagementGroupManagementPackVersion_ManagementGroupRowIdManagementPackVersionRowId'. Cannot insert duplicate key in object 'dbo.ManagementGroupManagementPackVersion'. The duplicate key value is (1, 7482, Jan 23 2014 4:33PM).
One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.Synchronization.Configuration
This event is telling us that Operations Manager is trying to synchronize a Management Pack that is already in the Data Warehouse and it is becoming stuck because of this.
So how could I have prevented this? What should I have done?
To avoid this eventuality you need to ensure that when you restore the OperationsManager DB you also restore the OperationsManagerDW from backups taken at the same time.
It is possible you can get away with just restoring the OperationsManager DB but this will only be fault free if you have not changed any management packs in the time between taking the backup of the OperationsManager DB and the restore. If you have very tightly controlled Change Management procedures and good change logging, then you may be able to confirm it is safe to just restore the OperationsManager DB, but beware of the risks, and also be aware of what actually constitutes a Management Pack change.
Many configuration settings within Operations Manager are actually stored in Management Packs. You may have made some changes using the console, such as editing a group, or an override, and without knowing it you have updated a Management Pack. So to be absolutely certain to avoid hitting synchronization issues you need to restore the DW at the same time as the Operations Manager DB.
OK- I know better for next time, but how do I recover from this right now?
The good news is that the recovery plan is relatively straightforward. In fact once you read this, you may decide that it is always worth taking the risk of just restoring the OperationsManager DB and adding on these steps afterwards if necessary.
In order to recover you first need to identify the MPs that have changed that are blocking the sync process. In order to do that, you need to run a SQL query to identify the blocking MP, export and update the version number and then reimport it.
Step 1 - Find the 31552 entry in the OperationsManager event log.
This will be logged on the Management server that is responsible for synchronization. In OpsMgr 2007 R2 that will be the RMS. In OpsMgr 2012 and OpsMgr 2012 R2 that job is taken by a member of the resource pool so you may need to check all of your management servers for it.
Log Name: Operations Manager
Source: Health Service Modules
Date: 25/02/2014 16:37:41
Event ID: 31552
Task Category: Data Warehouse
Level: Error
Computer: OMMS.domain.com
Description:
Failed to store data in the Data Warehouse.
Exception 'SqlException': Sql execution failed. Error 2627, Level 14, State 1, Procedure ManagementPackInstall, Line 2879, Message: Violation of UNIQUE KEY constraint 'UN_ManagementGroupManagementPackVersion_ManagementGroupRowIdManagementPackVersionRowId'. Cannot insert duplicate key in object 'dbo.ManagementGroupManagementPackVersion'. The duplicate key value is (1, 7482, Jan 23 2014 4:33PM).
Step 2 – Run the SQL query below to identify the MP
Note the numbers in the event details, highlighted in yellow above.
You need to plug these numbers into the following query to be run against the Datawarehouse in order to identify the blocking MP.
Use OperationsManagerDW
Step 3 – Export the MP
Once you have identified the MP, you need to update the version, which ideally can be done by updating the Version in the console. E.g. change the last 0 to a 1.
Or if it is the Notifications Internal MP then you will need to export it and manually make the change before reimporting.
Step 4 – Edit the version number
Then edit it to up the version number. You can use notepad to do this if you like.
Step 5 – Import the updated MP
Save it and reimport it. This will unblock that particular MP from being synchronized.
Step 6 – Monitor the event log for 31552 or 31554 events and repeat steps 1 to 6 above if necessary
It is possible, however, that there are other MPs that have also been changed between the backup and restore points, so you may need to repeat the process of checking for 31552 events and go through the steps above until you are all clear, and you receive a 31154 event indicating that configuration has now successfully been synchronized.
As always, if you would like any assistance in carrying out any of these steps, then please just open a case with us in Microsoft Support and we will be delighted to help you out.
Brian McDermott | Escalation Engineer | Microsoft CTS Management and Security Division
Get the latest System Center news on Facebook and Twitter :
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/
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/