SCCM Distribution Point Migration SQL Views and Status

One of the latest projects involved automating thousands of distribution point migration jobs. One of the runbooks in the Orchestrator automation for this project involved monitoring the progress of the migration jobs. The SCCM database has a view that tracks this progress and is an actual reflection of the status you see in the “Distribution Point Migration” section in the console.

The view is dbo.v_MIG_Job and this view combines data from several SQL tables, two of which are dbo.MIG_Job & dbo.DPUpgradeStatus. These two tables contain status updates about the distribution point migration jobs currently in progress as well as past migrations. As noted above, the console reflects that status and querying the v_MIG_Job view is a quick way to programmatically find the status of a migration.

Select [JobName],[Status],[DateStarted] From [dbo].[v_MIG_Job]

Distribution Point Migration Progress

The upper section shows the current status from the SCCM console while the lower part shows the SQL query and the result of that query. Additional columns exist in that view and can be queried if needed.

Below is a list of common values for the status column as well as the respective description:

Status ValueDescription
0Not started
2In progress
3331Pending on secondary site uninstallation
3074Reassigning distribution point
2561Completed update binaries
2562Updating binaries
2818Converting content
3073Completed reassign distribution point
2819Failed to convert content
515Failed (Network Timeout)
2563Failed to update binaries
3075Failed to reassign distribution point

The values and their description are self explanatory. “Pending on secondary site uninstallation” occurs if the distribution point being migrated is also a secondary site, the job will remain in this state until a data gather runs and SCCM picks up that this site server no longer exists in the migration source. You can start a synchronization of the source site from the console or through PowerShell by running:

Import-Module ($ENV:SMS_Admin_UI_Path.Substring(0,$ENV:SMS_Admin_UI_Path.Length-5) + '\ConfigurationManager.psd1')
Set-Location "$SiteCode':'"
Sync-CMMigrationSource -ForceWildcardHandling

This will initiate a Gather Data on all the sources configured in the source hierarchy. Depending on the number of source sites, packages and other data, this could take a very long time. If you would like to gather data from only one source site through SCCM PowerShell cmdlets, unfortunately, Microsoft does not yet have a cmdlet to accomplish that and Sync-CMMigrationSource does not accept parameters. However, you can accomplish this by using WMI in a PowerShell script:

Get-WmiObject -ComputerName $CASServerName -Namespace "root\SMS\Site_$SiteCode" -Class SMS_MigrationSiteMapping -Filter "SourceSiteFQDN like '$SourceSiteServerName'" | ForEach-Object {Invoke-WmiMethod -Path $_.__Path -Name Sync}

This will allow you to sync a particular source site of your choosing if you desire so.

Back to the status values in the SQL view, the Runbook to detect them was created by implementing a PowerShell function that can be called to provide a continuously updated distribution point migration status. I used the return values to monitor the progress and perform further automated configuration changes such as setting rate limits and adding the migrated distribution point to a DP group once the migration was finished (more on this in a later article).

function CheckDPStatus()

$DP12Query = "SELECT * FROM CM_$SiteCode.dbo.v_MIG_JOB WHERE JobName = '$ServerName'"

$Connection = New-Object System.Data.SqlClient.SqlConnection
$ConnectionString = "Server=$($CM2012SQLServer);Database=$($CM2012Database);Integrated Security=True;Connect Timeout=$($ConnectionTimeout)"
$Connection.ConnectionString = $ConnectionString
$CMD = New-Object System.Data.SqlClient.SqlCommand($DP12Query,$Connection)
$CMD.CommandTimeout = $QueryTimeout
$DataSet = New-Object System.Data.DataSet
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($CMD)
$Dataset = New-Object
$DataAdapter.fill($DataSet) | Out-Null
$DP = @($DataSet)
$ReturnValue = $DP.Status

Return $ReturnValue

A PowerShell script was implemented to make use of this function and to track the status changes as well as perform actions and log updates as needed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.