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 |
---|---|
Describes how to stop data warehouse jobs and how to stop the Self-Service Portal. |
|
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.
-----