Upgrade to System Center 2012 R2 - Service Manager

Applies To: System Center 2012 R2 Service Manager, System Center 2012 SP1 - Service Manager

You cannot start an upgrade to Service Manager in System Center 2012 R2 if any data warehouse jobs or workflows are running. You can use the procedures in this section to stop the schedules for data warehouse jobs and wait for them to finish before you upgrade the data warehouse management server. Before you upgrade the Service Manager management server, stop the Self-Service Portal, if it is installed, and then wait 10 minutes to let any running workflows finish before you start the upgrade.

Warning

The data warehouse stops working after upgrading Service Manager in System Center 2012 SP1 to System Center 2012 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

Complete the procedures in the following table to upgrade to Service Manager in System Center 2012 R2.

Task Description

How to Prepare Service Manager 2012 SP1 for Upgrade to R2

Describes how to stop data warehouse jobs and how to stop the Self-Service Portal.

How to Upgrade to System Center 2012 R2 - Service Manager

Describes how to upgrade the data warehouse management server, the Service Manager management server, and the Self-Service Portal.

-----
For additional resources, see Information and Support for System Center 2012.

Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
-----