How to Prepare Service Manager 2012 for Upgrade to SP1

 

Updated: May 13, 2016

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

This topic describes how to prepare your System Center 2012 – Service Manager environment for an upgrade. To do this, perform the following procedures for upgrading the data warehouse management server:

  1. List the data warehouse jobs that are running.

  2. Disable the data warehouse job schedules.

  3. Confirm that the data warehouse jobs have stopped running.

When the data warehouse jobs have completed, start the upgrade of the data warehouse management server.

After the data warehouse has been upgraded, perform the following procedures on the first Service Manager management server:

  1. Wait 10 minutes, and then start the upgrade of the Service Manager management server.

To list the data warehouse jobs by using Windows PowerShell cmdlets

  1. On the computer that hosts the data warehouse management server, click Start, click All Programs, click Microsoft System Center 2012, and then click Service Manager Shell.

  2. Type the following commands, and then press ENTER after each command:

    Set-ExecutionPolicy –force RemoteSigned  
    
    
    cd 'C:\Program Files\Microsoft System Center 2012\Service Manager'  
    Import-Module .\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1  
    
    
    
    Get-SCDWJob  
    
    
  3. A list of the data warehouse jobs appears. Use this list in the next procedure, "To disable data warehouse job schedules by using Windows PowerShell cmdlets.”

To disable data warehouse job schedules by using Windows PowerShell cmdlets

  1. Type the following commands, and then press ENTER after each command:

    Disable-SCDWJobSchedule –JobName Extract_<data warehouse management group name>  
    
    
    Disable-SCDWJobSchedule –JobName Extract_<Service Manager management group name>  
    
    
    Disable-SCDWJobSchedule –JobName Transform.Common  
    
    
    Disable-SCDWJobSchedule –JobName Load.Common  
    
    
    Disable-SCDWJobSchedule –JobName DWMaintenance  
    
    
    Disable-SCDWJobSchedule –JobName MPSyncJob  
    
    
    Start-SCDWJob –JobName MPSyncJob  
    
    

    The last command to start the MPSyncJob will enable the extraction, transformation, and load (ETL) jobs to run to completion. After that, because all the schedules have been disabled, the jobs will stop. To close the Windows PowerShell window, type exit.

To confirm that the data warehouse jobs have stopped running

  1. In the Service Manager console, click Data Warehouse.

  2. In the Data Warehouse pane, expand Data Warehouse, and then click Data Warehouse Jobs.

  3. In the Data Warehouse Jobs pane, observe the Status column for each data warehouse job. When the status for each job is listed as Not Started, proceed to the next procedure to stop the Self-Service Portal. If no Self-Service Portal exists in your environment, you can start the upgrade process in How to Upgrade to System Center 2012 SP1 - Service Manager.

To prevent MPSync jobs from failing

  • Run the SQL script below on the DWRepository database to get the SQL scripts that drop and add a constraint on the primary key in fact tables in the DWRepository database to correct the problem

    ;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,  
                RANK() OVER ( PARTITION BY p.WarehouseEntityName ORDER BY PartitionName ASC ) AS RK  
        FROM    etl.TablePartition p  
           join FactName f on p.WarehouseEntityName = f.WarehouseEntityName  
    )  
    , FactPKList  
    AS (  
        SELECT  f.WarehouseEntityName, a.TABLE_NAME, a.COLUMN_NAME, b.CONSTRAINT_NAME, f.RK,  
                CASE WHEN b.CONSTRAINT_NAME = 'PK_' + f.WarehouseEntityName THEN 1 ELSE 0 END AS DefaultConstraints  
        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'  
    )  
    , FactWithoutDefaultConstraints  
    AS (  
        SELECT  a.*  
        FROM    FactPKList a  
        LEFT JOIN FactPKList b ON b.WarehouseEntityName = a.WarehouseEntityName AND b.DefaultConstraints = 1  
        WHERE   b.WarehouseEntityName IS NULL AND a.RK = 1  
    )  
    , FactPKListStr  
    AS (  
        SELECT  DISTINCT f1.WarehouseEntityName, f1.TABLE_NAME, f1.CONSTRAINT_NAME, F.COLUMN_NAME AS PKList  
        FROM    FactWithoutDefaultConstraints f1  
        CROSS APPLY (  
                        SELECT  '[' + COLUMN_NAME + '],'  
                        FROM    FactWithoutDefaultConstraints f2  
                        WHERE   f2.TABLE_NAME = f1.TABLE_NAME  
                        ORDER BY COLUMN_NAME  
                    FOR  
                       XML PATH('')  
                    ) AS F (COLUMN_NAME)  
    )  
    SELECT  'ALTER TABLE [dbo].[' + f.TABLE_NAME + '] DROP CONSTRAINT [' + f.CONSTRAINT_NAME + ']' + CHAR(13) + CHAR(10) +  
            'ALTER TABLE [dbo].[' + f.TABLE_NAME + '] ADD CONSTRAINT [PK_' + f.WarehouseEntityName + '] PRIMARY KEY NONCLUSTERED (' + SUBSTRING(f.PKList, 1, LEN(f.PKList) -1) + ')' + CHAR(13) + CHAR(10)  
    FROM    FactPKListStr f  
    
    
    
Show: