CM12 R2 SP1 not ready for Distributed Views

If you have a CAS (you shouldn't), and if you have enabled distributed views, you might want to hold off from upgrading to SP1 for CM12 R2.

It sounds like we'll need a hotfix for the upgrade so that certain tables and views are checked for during the upgrade. When you enable distributed views, views are created to show data left on primary sites that is not on the CAS. The upgrade isn't expecting those so when it goes to recreate tables, the name is in use (as are some keys in indexes) and the upgrade fails. The ugly part for me is that it failed so far in that running recovery using my SQL backup wouldn't work.

So will this fail for you too? I'm not sure. It might just be my certain layout that wasn't tested. Let me describe it.

CAS

PR1 PR2

PR1 has just the hardware inventory node enabled for distributed views.

PR2 has all three links enabled.

How much you extend your inventory affects the total number of distributed views created. In my case, I had 321 of them. But it's just the PR2 tables and views that the upgrade got upset over; the ones where one site had all links enabled for DV. What if PR1 had all three links enabled too? Would I have had the problem? What if PR2 had only the hardware inventory node enabled? Would I have had the problem? I don't know. Will you have the problem? I wouldn't take the chance.

To get past this issue, I nuked some tables and views:

DROP TABLE [dbo].[CollectedFiles_RCM]
DROP TABLE [dbo].[FileUsageSummary_RCM]
DROP TABLE [dbo].[FileUsageSummaryIntervals_RCM]
DROP TABLE [dbo].[MonthlyUsageSummary_RCM]
DROP TABLE [dbo].[SoftwareFile_RCM]
DROP TABLE [dbo].[SoftwareFilePath_RCM]
DROP TABLE [dbo].[SoftwareInventory_RCM]
DROP TABLE [dbo].[SoftwareInventoryStatus_RCM]
DROP TABLE [dbo].[SoftwareProduct_RCM]
DROP TABLE [dbo].[SoftwareProductMap_RCM]
DROP TABLE [dbo].[SummarizationInterval_RCM]
DROP VIEW [dbo].[CollectedFiles]
DROP VIEW [dbo].[FileUsageSummary]
DROP VIEW [dbo].[FileUsageSummaryIntervals]
DROP VIEW [dbo].[MonthlyUsageSummary]
DROP VIEW [dbo].[SoftwareFile]
DROP VIEW [dbo].[SoftwareFilePath]
DROP VIEW [dbo].[SoftwareInventory]
DROP VIEW [dbo].[SoftwareInventoryStatus]
DROP VIEW [dbo].[SoftwareProduct]
DROP VIEW [dbo].[SoftwareProductMap]
DROP VIEW [dbo].[SummarizationInterval]
DROP VIEW [_sde].[v_GeneralInfo]
DROP VIEW [_sde].[v_GeneralInfoEx]
DROP VIEW [_sde].[v_GS_AppInstalls]
DROP VIEW [_sde].[v_HR_NSV]
DROP VIEW [_sde].[v_MachineUsage]

So after restoring the CM database from backup, dropping the views and tables above, and then running the upgrade, it finally took. The CAS is now at SP1 and replication is looking good. The only reason I'm posting the views and tables above is in case someone else already got themselves in trouble. I wouldn't do this unless it's already too late. And those last views we created in our own schema, but the upgrade still doesn't like them so if you have any of your own, you might want to makes copies, blast them, and put them back after the upgrade.

Long story short, if you're using distributed views, I'd recommend you wait on SP1 until we hear from Microsoft.

Update: Notice that the views above are all related to the software inventory and software metering link. As I mentioned in the lab, one of my site had all three links set for DV and one primary was marked for DV for just hardware inventory. Well in production we have only the hardware inventory link enabled for DV so we decided to move forward with SP1 there and it worked fine. So if there is an issue, it would only be with the software inventory and software metering link. Now is there an issue? I sent our database off to Microsoft but never heard back.