Installing Microsoft System Center Service Manager 2012. Step by Step guide- Part 6 “Upgrading to R2”

Leave a comment

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

  1. 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'
    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
    , 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
        SELECT  TOP 1
                @PartitionName = PartitionName,
                @Query = Query
        FROM    #PKFixQueries
        PRINT   @Query
        DELETE  #PKFixQueries
        WHERE   PartitionName = @PartitionName

    Now remember to apply the SQL Scripts on all the mentioned Databases not only the one shown in the above snapshot.

  2. Download Update Rollup 2 for System Center 2012 SP1 Service Manager (KB2802159)
    ow 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 meFailed 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”
    “Last Counter”=dword:00001fa8
    “Last Help”=dword:00001fa9
    “First Counter”=dword:00001f88
    “First Help”=dword:00001f89
    “Object List”=”8072”
    Once you have the registry key just double click on it, and then start the Update Rollup installation
    1And we are done…
  3. 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 🙂

  4. 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.

10Hope this was a useful series. see you later with another articles 🙂

Related Articles

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”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s