升級至 System Center 2012 R2 - Service Manager

更新日期: 2014年1月

適用於: System Center 2012 R2 Service Manager, System Center 2012 SP1 - Service Manager

如果有任何資料倉儲作業或工作流程執行中,您無法啟動 System Center 2012 R2 中的 Service Manager 升級作業。您可以使用本節中的程序來停止資料倉儲作業排程並等候排程完成,然後再升級資料倉儲管理伺服器。在升級 Service Manager 管理伺服器之前,請停止 自助入口網站 (若已安裝) 並等候 10 分鐘,待所有執行中的工作流程完成後再啟動升級作業。

Warning警告
資料倉儲在將 System Center 2012 SP1 中的 Service Manager 升級至 System Center 2012 R2 後,因為資料倉儲需要進行實體升級而停止運作。

若要避免此問題發生,請針對下列各個資料倉儲資料庫執行下列 SQL 指令碼:DW Repository、DW DataMart、CM DataMart 和 OM DataMart。如果在資料倉儲升級後可以使用這種方法解決問題,請繼續進行失敗的管理組件升級程序,部署從 Service Manager 主控台擷取的資料倉儲管理組件清單。

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

請完成下表中的程序,以便升級為 System Center 2012 R2 中的 Service Manager。

 

工作 說明

如何準備 System Center 2012 SP1 以升級為 R2

說明如何停止資料倉儲作業以及如何停止自助入口網站。

如何升級至 System Center 2012 R2 - Service Manager

說明如何升級資料倉儲管理伺服器、Service Manager 管理伺服器和自助入口網站。

-----
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.
-----
顯示: