What volume to migrate first? - System Center: Data Protection Manager Engineering Team Blog - Site Home - TechNet Blogs

What volume to migrate first?

What volume to migrate first?

  • Comments 8
  • Likes

Sid Ramesh wrote a fantastic script that provides great LDM statistics amongst which how many extents a data source uses. This helps to determine which volume to migrate first to reduce extents if there is a need to. Sample output is shown below followed by the actual script.

A companion post on LDM information can be found here.

image

#begin script

#---------------------------------

#LdmStats.Ps1

param ()
#region queries
#-- Count only extents from dynamic disks
$ExtentCountCmd = "select count(*) as  TotalNumberOfDynamicExtents from tbl_SPM_Extent extent where extent.DiskID in (select DiskID from tbl_SPM_Disk where DiskType = 1) "
#-- Count the number of unique PhysicalReplicas
$PhysReplCountCmd = "select physicalreplicaid as PhysicalReplicaId, COUNT(*) as Count from tbl_PRM_LogicalReplica where physicalreplicaid is not null and datasourceid is not null group by PhysicalReplicaId"
#-- Count the number of datasources
$DScountCmd = "select COUNT(*) as TotalNumberOfDatasources from tbl_IM_Datasource where ProtectedGroupId is not null"
#-- Count the number of volumes
$VolCountCmd = "select COUNT(*) as NumberOfVolumes from tbl_SPM_Volume where VolumeSetID is not null"
#-- Get the LDM alerts
$LDMAlertsCmd = "select AlertId, Type, OccuredSince, Resolution, ResolvedTime from tbl_AHP_Alerts where Type = 63 or Type = 64 order by OccuredSince desc "
#-- Get the datasources with the maximum extents (useful for migration)
$DSwithExtentsCmd = "SELECT (select DataSourceName from tbl_IM_Datasource ds2 where DataSourceId = DS.DataSourceId) as DatasourceName,
(select pg.FriendlyName from tbl_IM_Datasource ds2 join tbl_IM_ProtectedGroup pg on ds2.ProtectedGroupId = pg.ProtectedGroupId
where DataSourceId = DS.DataSourceId) as ProtectionGroupName,
COUNT(Extent.DiskID) as NumberOfExtents, replica.PhysicalReplicaId as PhysicalReplicaId, DS.DatasourceId as DatasourceId
FROM tbl_SPM_Extent Extent WITH (NOLOCK) 
    JOIN dbo.tbl_SPM_Volume Volume WITH (NOLOCK) 
        ON Extent.GuidName = Volume.GuidName 
    JOIN dbo.tbl_PRM_LogicalReplica Replica WITH (NOLOCK) 
        ON Replica.PhysicalReplicaId = Volume.VolumeSetID 
    JOIN dbo.tbl_IM_Datasource DS WITH (NOLOCK) 
        ON Replica.DataSourceId = DS.DataSourceId
GROUP BY DS.DataSourceId, Replica.PhysicalReplicaId
Order by NumberOfExtents desc"
#-- Get all the Replica and SC disk threshold exceeded alerts along with the corresponding datasources
$GetDiskAlertsCmd = "select aa.AlertId, ra.DatasourceId, Type as AlertType, OccuredSince, Resolution, ResolvedTime from tbl_AHP_Alerts aa 
    JOIN tbl_PRM_ReplicaAlerts ra WITH (NOLOCK) 
        ON aa.AlertId = ra.AlertId
where Type = 31 or Type = 36"
#endregion
function DisplayAndSelect {
    param ($list, $item)
    # unified forced select from list by index number
    Write-Host ""
    Write-Host "Select $($item.toupper()) from list below:" 
    for ($i = 0; $i -lt $list.count; $i++) {
        write-host "`t -> [$i] $($list[$i])" 
    }
    Write-Host ""
    $i = – 1
    while (($i -lt 0) -or ($i -ge $list.count)) {
        $now = (Get-Date).ToString($format)
        $i = [int](Read-Host "[$now] Enter index number")
    }
    write-host "Selected $($list[$i])`n" 
    return $list[$i]
}
function GetSqlServers {
    # return SQL instances that the SQL browser service can find
    write-host "Searching for SQL service instances, this may take a while..." 
    return [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
}
#START
$version = "1.6"
write-host ""
Write-Host ("=" * 30) -ForegroundColor cyan
Write-Host "LdmStats version $version" -ForegroundColor yellow
Write-Host  ("=" * 30) -ForegroundColor cyan
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers") 
#ensure browser runs
$sb = Get-Service | ? {$_.name -match "sqlbrowser"}
if (!$sb) {Throw "No SQL browser service found!"}
if ($sb.status -ne "running") {$sb.start; sleep 500}
# get sql server list
$SQLinstances = @(GetSqlServers)
$tmplist = @()
# build SERVER\INSTANCE format
$SQLinstances | foreach {$tmplist += "$($_.servername)\$($_.instancename)"}
$SQLinstances = @($tmplist)
$sqlserver = DisplayAndSelect $SQLinstances "SQLserver"
$dpmservername = (&hostname)
Write-Host "Connecting [$sqlserver]..."
$srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
$db = $srvr.Databases["DPMDB"]
$ExtentCount =   $db.ExecuteWithResults($ExtentcountCmd).Tables[0].rows[0].TotalNumberOfDynamicExtents
$DSCount =  $db.ExecuteWithResults($DScountCmd).Tables[0].rows[0].TotalNumberOfDatasources
$volcount = $db.ExecuteWithResults($VolcountCmd).Tables[0].rows[0].NumberOfVolumes
$dpmRemaining = [math]::truncate(600 - $volcount)
$diskcount = (Get-DPMDisk $dpmservername).count
Write-Host "Total disks".PadRight(20)  ": $diskcount"
Write-Host "Total volumes".PadRight(20) ": $volcount"
Write-Host "Total extents".PadRight(20) ": $ExtentCount"
Write-Host "Total data sources".PadRight(20) ": $DSCount"
$usedslots = 1 + $diskcount + (2*$volcount) + $ExtentCount
$slotsRemaining = 2960-$usedslots
$volRemaining = [math]::Truncate($slotsRemaining/3)
if ($dpmRemaining -lt $volRemaining) {$dsRemaining = [math]::Truncate($dpmRemaining/2)}
else {$dsRemaining = [math]::Truncate($volRemaining/2)}
Write-Host "Number of non-colocated data sources that can still be added: $dsRemaining"
Write-Host "`nData source extent list..."
$DSwithExtentsTable = $db.ExecuteWithResults($DSwithExtentsCmd).Tables[0]
$DSwithExtentsRows =   $DSwithExtentsTable.rows
$physicalReplicasPrinted = @{}
foreach ($row in $DSwithExtentsRows)

    if ($physicalReplicasPrinted.Contains($row["PhysicalReplicaId"])) { 
        $row.Delete() 
    } else { 
        $physicalReplicasPrinted[$row["PhysicalReplicaId"]] = 1; 
    }
}
$DSwithExtentsTable.AcceptChanges()
$DSwithExtentsRows | ft –AutoSize
Write-Host "`nReplica colocation counts..."
$PhysReplCount =   $db.ExecuteWithResults($PhysReplCountCmd).Tables[0].rows
$PhysReplCount | ft –AutoSize
Write-Host "`nLDM alerts list..."
$LDMAlerts =   $db.ExecuteWithResults($LDMAlertsCmd).Tables[0].rows
if ($LDMAlerts.Count -gt 0 ) { 
    $LDMAlerts | ft –AutoSize
}
else {write-host "None found!" -f white}
Write-Host "`nDone!"
#---------------------------------

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Still can't access the registry settings.  Also, I had to delete all the lines that started with #.  I had problems with Powershell 1.0 so I upgraded to 2.0 and that version has the above problems.

  • 2) Can't get the DB correctly if the instance name is MS$DPM2007$ (default)

    This is still an issue, even supplying the data from the registry.

  • Corrected.

    It does not use registry anymore and asks you which reachable SQL instance to use

  • This script gives me DatasourceName,  ProtectionGroupName, NumberOfExtents, PhysicalReplicaId and DatasourceId. Unfortunately not the Name of the server itself. In my case I have all the fileservers in one group. It would be nice if the script could be extended to also display the server name.    

  • I have added a line to the script and have now the servername listed, too.

  • I also need the servername listed. what line did you add and were in the code?

  • How can I export the results of this script?

  • I am unable to use this script effectively. I have multiple DPM servers tying into a seperate SQL server therefore,none of the DPM servers have the sql browser installed. Additionally, the SQL server doesn't have the DPM powershell installed and I cannot install it on the SQL server. Are there any work arounds to get this script to work in this situation?