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.
$SMExtractJobName = "Extract_SM_Name?"
$DWExtractJobName = "Extract_DW_Name?"$MPSync="MPSyncJob"
{
$JobRunning = 1
while($JobRunning -eq 1)
{
$JobRunning = 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
}
{
$Job = Get-SCDWJob -JobName $JobName -Computer $Computer
$JobStatus = $Job.Status
return $JobStatus
}
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
