It’s time to upgrade our Service Manager 2012 SP1 isntallation to R2, in our last article i have introduced the steps to upgrade your Service Manager installation to SP1 along with the pre-installation steps you need to avoid a failed upgrade.
Now upgrading to R2 takes the same steps done in upgrading to SP1, there is no change in the upgrade order or steps including stopping the Data warehouse jobs and everything. only a couple of new steps needs to be done to guarantee a successful upgrade.
So now am not going to explain the repeated steps in details, i will just go for an overview and leave the previous article for the details. you can always go check them out here Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 5 “Upgrading to SP1”.
Upgrade order and timing. Reference “http://technet.microsoft.com/en-us/library/dn520875.aspx“
The order of your upgrades is important. Perform the upgrades by using the following steps:
- Back up your databases and your management packs.
- Upgrade the data warehouse management server. You must stop the data warehouse jobs, and you will not be able to start them again until after you have completed the upgrade.
- After the upgrade to the data warehouse management server is complete, upgrade the initial Service Manager management server. If you created more than one Service Manager management server, the initial Service Manager management server is the first one that you created.
- Upgrade the Service Manager consoles and any additional Service Manager management servers.
- Restart the data warehouse jobs.
- Deploy the new Self-Service Portal.
The Upgrade Process
- Warning 🙂 The data warehouse stops working after upgrading Service Manager in System Center 2012 SP1 to System Center2012 R2 due to a Data Warehouse fact entity upgrade.
To prevent this issue from occurring, run the following SQL script for each of the following data warehouse databases: DW Repository, DW DataMart, CM DataMart, and OM DataMart. If this workaround is applied after data warehouse upgrade, resume the failed management pack upgrade process deploy captured in the list of Data Warehouse Management Packs from the Service Manager console.
IF OBJECT_ID('tempdb..#PKFixQueries') IS NOT NULL DROP TABLE #PKFixQueries ;WITH FactName AS ( SELECT w.WarehouseEntityName FROM etl.WarehouseEntity w JOIN etl.WarehouseEntityType t ON w.WarehouseEntityTypeId = t.WarehouseEntityTypeId WHERE t.WarehouseEntityTypeName = 'Fact' ),FactList AS ( SELECT PartitionName, p.WarehouseEntityName FROM etl.TablePartition p JOIN FactName f ON p.WarehouseEntityName = f.WarehouseEntityName ) , FactWithPK AS ( SELECT f.WarehouseEntityName, f.PartitionName, b.CONSTRAINT_NAME, a.COLUMN_NAME FROM FactList f JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON f.PartitionName = a.TABLE_NAME JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND b.CONSTRAINT_TYPE = 'Primary key' ) , FactWithDefaultOrNoPK AS ( SELECT DISTINCT f.WarehouseEntityName, f.PartitionName , 'PK_' + f.WarehouseEntityName AS DefaultPKConstraint , 'PK_' + f.PartitionName AS NewPKConstraint FROM FactList f LEFT JOIN FactWithPK pkf ON pkf.WarehouseEntityName = f.WarehouseEntityName AND pkf.PartitionName = f.PartitionName WHERE pkf.WarehouseEntityName IS NULL OR pkf.CONSTRAINT_NAME = 'PK_' + f.WarehouseEntityName ) , FactPKList AS ( SELECT DISTINCT f.WarehouseEntityName, f.COLUMN_NAME FROM FactWithPK f ) , FactPKListStr AS ( SELECT DISTINCT f1.WarehouseEntityName, F.COLUMN_NAME AS PKList FROM FactPKList f1 CROSS APPLY ( SELECT '[' + COLUMN_NAME + '],' FROM FactPKList f2 WHERE f2.WarehouseEntityName = f1.WarehouseEntityName ORDER BY COLUMN_NAME FOR XML PATH('') ) AS F (COLUMN_NAME) ) SELECT f.PartitionName, '----------------------------- [' + f.PartitionName + '] -----------------------------' + CHAR(13) + 'IF OBJECT_ID(''[' + f.DefaultPKConstraint + ']'') IS NOT NULL' + CHAR(13) + 'BEGIN' + CHAR(13) + ' ALTER TABLE [dbo].[' + f.PartitionName + '] DROP CONSTRAINT [' + f.DefaultPKConstraint + ']' + CHAR(13) + 'END' + CHAR(13) + CHAR(13) + 'IF OBJECT_ID(''[' + f.NewPKConstraint + ']'') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) + ' ALTER TABLE [dbo].[' + f.PartitionName + '] ADD CONSTRAINT [' + f.NewPKConstraint + '] PRIMARY KEY NONCLUSTERED (' + SUBSTRING(pk.PKList, 1, LEN(pk.PKList) -1) + ')' + CHAR(13) + 'END' AS Query INTO #PKFixQueries FROM FactWithDefaultOrNoPK f JOIN FactPKListStr pk ON pk.WarehouseEntityName = f.WarehouseEntityName DECLARE @PartitionName NVARCHAR(MAX), @Query NVARCHAR(MAX) WHILE EXISTS (SELECT 1 FROM #PKFixQueries) BEGIN SELECT TOP 1 @PartitionName = PartitionName, @Query = Query FROM #PKFixQueries PRINT @Query EXEC(@Query) DELETE #PKFixQueries WHERE PartitionName = @PartitionName END
Now remember to apply the SQL Scripts on all the mentioned Databases not only the one shown in the above snapshot.
- Download Update Rollup 2 for System Center 2012 SP1 Service Manager (KB2802159)
Now before you go ahead with installing the Update Rollup, there is a problem that might cause the installation to fail with an error error occured while executing custom action: “_Installhealthserviceperfcountersforpatching”, this might happen because of a missing registry entry for the MOMConnector Performance counters.
Thanks to this Blog Post it solved it for me “Failed to Apply UR2 to SCSM 2012 SP1 – Performance Counters not found” . all you need to do is create a missing Registry key. unfortunatley i am unable to attach files in this blog so either download it from the previous link or just create a text file and paste the following content in it then save it as .reg extension.
Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MOMConnector][HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MOMConnector\Performance]
“Library”=”C:\\Program Files\\Microsoft System Center 2012\\Service Manager\\MOMConnectorPerformance.dll”
Once you have the registry key just double click on it, and then start the Update Rollup installation
And we are done…
- Now before we start the installation of the R2 upgrade make sure to follow the below steps which are details in Part 5 of this series:
Make sure you made the configurations in “How to Work Around Configuration Service Startup Issues“ to avoid any startup timeout issues.
Disable data warehouse job schedules by using Windows PowerShell cmdlets.
Start the installation 🙂 .. ofcourse with the agreed order, Data warehouse , Management Server, Console and then any other components “SSP”
You should be fine with upgrading all the components. Hopefully 🙂
- Follow the After Upgrading to System Center 2012 SP1 Section in the previous Article.
That would be all .. with this part we have finished upgrade our Service Manager 2012 deployment to the latest release.
Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 1 “Introduction”
Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 2 “installation”
Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 3 “installation”
Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 4 “installation”
Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 5 “Upgrading to SP1”