Data Warehouse problems after upgrading to SP1

Recently I discovered that an otherwise working SCSM installation had begun to fail the MP synchronization after upgrading to SP1.

The following error was logged in the event log:

Event ID: 33333

Description:

Data Access Layer rejected retry on SqlError:
Request: Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2013_Jan]'') IS NULL
BEGIN
  CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)
 Class: 16
 Number: 1779
 Message: Table 'WorkItemAffectedUserFact_2013_Jan' already has a primary key defined on it.

This is caused by a problem with the management packs trying to sync to the Data Warehouse.

What's happening is that some of the data warehouse management packs are trying to redeploy after the upgrade to SP1. Several other management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state. Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it

To recover from this:

1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

2. For each primary key that shows up in your event logs with the message e.g. "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it. ", delete the primary key from the DWRepository DB. For me there were around 70 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail. This can be done with a T-SQL statement such as the following:

USE DWRepository ALTER TABLEdbo.BillableTimeHasWorkingUserFact_2013_Jan dropconstraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

Note that your fact table might be a different year and/or month than shown in the example above.

3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue. You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

It is very important that you check your event logs for a listing to see which tables and primary keys are affected. If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

4. When cleaned up all Primary key errors and started the redelpoy, new errors migth occour and you then need to start with step 2 again, until all the MPs are synced with success

Thanks to Andrew Barton for creating the workaround.