Support Tip: Data Warehouse synchronization failures following restore of the OperationsManager DB

Support Tip: Data Warehouse synchronization failures following restore of the OperationsManager DB

  • Comments 3
  • Likes

~ Brian McDermott

ToolsHow 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

  select ManagementPackSystemName, ManagementPackDefaultName, ManagementPackDefaultDescription
from dbo.ManagementGroupManagementPackVersion mgmpv
  join ManagementPackVersion mpv
  on mpv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId
  join ManagementPack mp
  on mpv.ManagementPackRowId = mp.ManagementPackRowId
    WHERE [ManagementGroupRowId] = 1
  and mpv.[ManagementPackVersionRowId] = 7482

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.

clip_image001

Or if it is the Notifications Internal MP then you will need to export it and manually make the change before reimporting.

clip_image003

Step 4 – Edit the version number

Then edit it to up the version number. You can use notepad to do this if you like.

clip_image005

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:

clip_image001 clip_image002

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/

App-V Team blog: http://blogs.technet.com/appv/
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/

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Great article!
    The only issue I have with it is that it is not the management servers that sync data to the data warehouse. They will update the Management Packs that are installed, but synchronization of the actual data is done by the data warehouse (sp ManageEntityChange is an example of this on the DW).

  • Just for your info no need to export the MP just update in the console directly if they are unsealed. I ran through this is exact problem only a month back or so and you can find umpteen Mp's need to be done. Cheers

  • Thank you very much. It helped me to find the cause of my error.