Data Synchronization issue between ServiceManager Database and DWDatamart Database in SCSM 2012

Data Synchronization issue between ServiceManager Database and DWDatamart Database in SCSM 2012

As you know Service Manager is an online transactional database and Microsoft recommends not to run queries on transactional database. For reporting purpose we can use DWDataMart DB.

There are 4 jobs which has to run in the order for the synchronization to happen

·         Extract_SM

·         Extract_DW

·         Load.COmmon

·         Transform.Common

You can schedule the job in such an order that each runs after other in the above order. This took some time to understand the synchronzization between ServiceManager and DWDatamart happens through this ETL Process.

I use this script  which runs the job in each order and waits for the current running job to complete before starting another one. You can automate this using task scheduler to run the powershell script on an hourly or daily basis if the out of box schedule doesn’t work.

 $DWComputer = "DW Server Name"
$SMExtractJobName = "Extract_SM_Name?"
$DWExtractJobName = "Extract_DW_Name?"$MPSync="MPSyncJob"

Import-Module 'C:\Program Files\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1'

function Start-Job ($JobName, $Computer)
{
    $JobRunning = 1
    while($JobRunning -eq 1)
    {
        $JobRunning = Start-Job-Internal $JobName $Computer
    }
}

function Start-Job-Internal($JobName, $Computer)
{
    $JobStatus = Get-JobStatus $JobName
    if($JobStatus -eq "Not Started")
    {
        Write-Host "Starting the $JobName Job..."
        Start-SCDWJob -JobName $JobName -Computer $Computer
        Start-Sleep -s 5
    }
    elseif($JobStatus -eq "Running")
    {
        Write-Host "$JobName Job is already running.  Waiting 30 seconds and will call again."
        Start-Sleep -s 30
        return 1
    }
    else
    {
        Write-Host "Exiting since the job is in an unexpected status"
        exit
    }
   
    $JobStatus = "Running"
    while($JobStatus -eq "Running")
    {
        Write-Host "Waiting 30 seconds"
        Start-Sleep -s 30
        $JobStatus = Get-JobStatus $JobName
        Write-Host "$JobName Job Status: $JobStatus"
        if($JobStatus -ne "Running" -and $JobStatus -ne "Not Started")
        {
            Write-Host "Exiting since the job is in an unexpected status"
            exit
        }
    }
    return 0
}

function Get-JobStatus($JobName)
{
        $Job = Get-SCDWJob -JobName $JobName -Computer $Computer
        $JobStatus = $Job.Status
        return $JobStatus   
}

#ETL
Start-Job $MPSync $DWComputer
Start-Job $SMExtractJobName $DWComputer
Start-Job $MPSync $DWComputer
Start-Job $DWExtractJobName $DWComputer
Start-Job $MPSync $DWComputer
Start-Job "Transform.Common" $DWComputer
Start-Job $MPSync $DWComputer
Start-Job "Load.Common" $DWComputer