Resource to upgrade content databases

Description

@ThomasLie shared some code to upgrade content databases in parallel across all servers in the farm. Right now I am wondering what would be the best way to implement this functionality.

Requirements:

• Just like the ProductUpdate and ConfigWizard resources, you should be able to specify a window in which the resource can run.
• The current code runs the upgrade in parallel across all servers in the farm but is initiated from a single server. Since we specified that with SPDsc we only configure the local server unless this is technically not possible, we need to update the code to run a subset of databases on the local server. Thomas already added some logic to spread the upgrades across the servers which we can reuse.

I was thinking about possible locations/resources to add the code:

• SPContentDatabase: This resource is targeting a single database, which means you have to add a resource for each database in the farm to the config. Usually this resource is used on a specific server in the farm (so not all servers are checking the diesired state for the same content database), which means that
• New SPContentDatabaseUpgrade resource: This resource checks the upgrade status of a set of databases and runs an upgrade when required. When you have four SharePoint servers and eight content databases in the farm, the resource on server 1 will process database 1 and 5, server 2 will process database 2 and 6, etc.

Proposed properties

• IsSingleInstance
• Ensure
• DatabaseUpgradeDays
• DatabaseUpgradeTime
• (Potentially) NrOfUpgradeServers or another solution to know where the upgrade should be executed / determine which databases the resource should process

Special considerations or limitations

Would be great if we could add the resource to a specific set of servers in the farm and have them upgrade the farm. And we should check if there are any limitations, thresholds and safeguards in place to protect the farm against too many database upgrades at the same time.

Code

$masterServer = $data.AllNodes | ? { $_.IsMasterNode -eq $true }
$spServers = ($data.AllNodes | ? { $_.NodeName -ne "*" }).NodeName
                
#region Get all content databases
$psSession = New-PSSession -ComputerName $masterServer.NodeName -Credential $SPSetupAccount -Authentication CredSSP

if ($data.NonNodeData.DSCConfig.ParallelDatabaseUpgrade -eq $false)
{
    Invoke-Command -Session $psSession -ScriptBlock {
        $oldverbose = $VerbosePreference
        $VerbosePreference = "continue"
                               
        try
        {
            Add-PSSnapin Microsoft.SharePoint.PowerShell

            Write-Host -ForegroundColor DarkYellow "[$($Env:COMPUTERNAME)] Upgrading content databases..."

            Get-SPContentDatabase | Upgrade-SPContentDatabase -Confirm:$false -Verbose
        }
        catch
        {
            $errorPosition = $_.InvocationInfo.PSCommandPath + ": Line " + $_.InvocationInfo.ScriptLineNumber
            $logMessage = "[$($Env:COMPUTERNAME)] One or more errors occured while upgrading content databases, please check the logs! Error = {0}, Position: '{1}'" -f $_,$errorPosition
            Write-Host -ForegroundColor Red "$logMessage"
            return
        }
        finally
        {
            $VerbosePreference = $oldverbose
        }
    }

    #Write-Output " - [$($server.NodeName)] Removing PS Session"
    Remove-PSSession -Session $psSession
}
else
{
    #region Get all content databases
    $contentDBs = Invoke-Command -Session $psSession -ScriptBlock {
        try
        {
            Add-PSSnapin Microsoft.SharePoint.PowerShell
                                               
            $spDatabases = Get-SPContentDatabase
                                               
            return $spDatabases | % { $_.Name }
        }
        catch
        {
            $errorPosition = $_.InvocationInfo.PSCommandPath + ": Line " + $_.InvocationInfo.ScriptLineNumber
            $logMessage = "[$($Env:COMPUTERNAME)] One or more errors occured while getting content databases, please check the logs! Error = {0}, Position: '{1}'" -f $_,$errorPosition
            Write-Host -ForegroundColor Red "$logMessage"
            return
        }
        finally
        {
            $VerbosePreference = $oldverbose
        }
    }

    #Write-Output " - [$($server.NodeName)] Removing PS Session"
    Remove-PSSession -Session $psSession
    #endregion

    #region SParallelScript
    $SParallelScriptblock = {
        param($ServerName, $DatabaseName, $Credential)
        Add-PSSnapin Microsoft.SharePoint.PowerShell

        try
        {
            $script = {
                param($DatabaseName)
                                                               
                Add-PSSnapin Microsoft.SharePoint.PowerShell
                try
                {
                    $contentDatabase = Get-SPContentDatabase -Identity $DatabaseName
                                                                                              
                    if ($contentDatabase.NeedsUograde)
                    {
                        Upgrade-SPContentDatabase $DatabaseName -Confirm:$false
                                                                                                              
                        return "[$($Env:COMPUTERNAME)] DB Upgrade for database '$DatabaseName' succeeded"
                    }
                    else
                    { 
                        return "[$($Env:COMPUTERNAME)] Database '$DatabaseName' does not need to be upgraded"
                    }
                }
                catch
                {
                    $logMessage = "[$($Env:COMPUTERNAME)] DB Upgrade for database '$DatabaseName' failed! Error = {0}" -f $_
                                                                                              
                    return "$logMessage"
                }
            }
                                                               
            Write-Host -ForegroundColor Yellow "[$($Env:COMPUTERNAME)] Starting DB Upgrade for database '$DatabaseName' on server $ServerName"
                                                               
            $result = Invoke-Command -ComputerName $ServerName -Credential $Credential -Authentication Credssp -ScriptBlock $script -ArgumentList $DatabaseName
                                                               
            $message = $result
                                                               
            Write-Host -ForegroundColor Yellow $message
        }

        catch
        {
                        $errorPosition = $_.InvocationInfo.PSCommandPath + ": Line " + $_.InvocationInfo.ScriptLineNumber
                        $logMessage = "[$($Env:COMPUTERNAME)] One or more errors occured while upgrading content databases, please check the logs! Error = {0}, Position: '{1}'" -f $_,$errorPosition
                        Write-Host -ForegroundColor Red "$logMessage"
                        return
        }
        finally { }
    }
    #endregion
                
    # Create InitialSessionState with modules, path to module will be fetched from config database
    $InitialSessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()

    $RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $InitialSessionState, $Host)
    $RunspacePool.Open()
    $Jobs = @()

    #region Create parallel database upgrade jobs
    Write-Verbose "[$($Env:COMPUTERNAME)] Starting parallel database upgrade jobs..."

    for ($dbNum = 0; $dbNum -lt ($ContentDBs.Length); $dbNum++) 
    {
        try
        {
            $serverName = $spServers[$dbnum % $spServers.length]
            $Parameters = @{
                            ServerName = $serverName
                            DatabaseName = $contentDBs[$dbNum]
                            Credential = $SPSetupAccount
            }

            $PowerShell = [powershell]::Create()
            $PowerShell.RunspacePool = $RunspacePool
            $PowerShell.AddScript($ScriptBlock) | Out-Null
            $PowerShell.AddParameters($Parameters) | Out-Null
            $Jobs += , @($PowerShell, $PowerShell.BeginInvoke())
                                               
            Start-Sleep -Milliseconds 100
        }
        catch
        {
            $errorPosition = $_.InvocationInfo.PSCommandPath + ": Line " + $_.InvocationInfo.ScriptLineNumber
            $logMessage = "[$($Env:COMPUTERNAME)] Ran into an issue while adding parallel job! Error = {0}, Position: '{1}'" -f $_,$errorPosition
            Write-Verbose -Message "$logMessage"
        }
    }
    #endregion

    #region Check for compledetd jobs
    $jobsCount = $Jobs.Count
                
    while ($Jobs.IsCompleted -contains $false)
    {
        Start-Sleep 10

        $completed = $Jobs.IsCompleted | ? { $_ -eq $true }
        $completedCount = $completed.Count
        $percentageComplete = ($completedCount / $jobsCount).ToString("P")

        $message = "[$($Env:COMPUTERNAME)] Database upgrade jobs completed: {0} out of {1} ({2})" -f $completedCount,$jobsCount,$percentageComplete
        Write-Verbose -Message $message
    }

    $message = "[$($Env:COMPUTERNAME)] All parallel database upgrade jobs completed!"
    Write-Verbose -Message $message
    $error.Clear()
    #endregion

    #region JobResults
    foreach($job in $Jobs)
    {
        [string]$jobResult = $job[0].EndInvoke($job[1])
        $job[0].Dispose()

        if (![String]::IsNullOrEmpty($jobResult))
        {
            Write-Verbose -Message $jobResult
        }
    }
    #endregion

    #region Close runspace
    $RunspacePool.Close()
    $RunspacePool.Dispose()
    [GC]::Collect()
    #endregion
}

Start by locating the ProductUpdate, ConfigWizard, and SPContentDatabase resources mentioned in the issue, then review how they schedule work and target farm servers. Determine the resource scope, database distribution, concurrency safeguards, and configuration properties before implementation; done means the agreed design is implemented and its behavior is covered by tests.