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