Resource to upgrade content databases

[ykuijs]

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
}

[ThomasLie]

Hey guys,

in addition, we should consider the size of the content databases when upgrading. We have to make sure, large content databases will not be upgraded on the same server.

[ykuijs]

The issue with database size is that you cannot check the database size via SharePoint. The SPContentDatabase object has a size property, but that is the sum of both the data and transaction log files. So if that property says 50GB, it can be a 49GB data file with a 1 GB transaction log or a 25GB data file with a 25GB transaction log. So it isn't very useful to determine the database size.

The only way you have is to connect to SQL directly, which means you need permissions in SQL to request these sizes (not sure what permissions you need to request database sizes).

[ThomasLie]

The issue with database size is that you cannot check the database size via SharePoint. The SPContentDatabase object has a size property, but that is the sum of both the data and transaction log files. So if that property says 50GB, it can be a 49GB data file with a 1 GB transaction log or a 25GB data file with a 25GB transaction log. So it isn't very useful to determine the database size.

The only way you have is to connect to SQL directly, which means you need permissions in SQL to request these sizes (not sure what permissions you need to request database sizes).

Shouldn't it be possible to get the database size directly from the SQL server?

[ykuijs]

That is what I said in the last paragraph 😉

The only way you have is to connect to SQL directly, which means you need permissions in SQL to request these sizes (not sure what permissions you need to request database sizes).

This requires permissions in SQL server to request these values, however not sure exactly what. I think we can use the sp_databases stored procedure to request the size (size column is in KB). More info can be found here: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-databases-transact-sql?view=sql-server-ver15

This article indicates:
Permissions: Requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission, and must have access permission to the database. Cannot be denied VIEW ANY DEFINITION permission.

So we need to test if a default admin/setup account (which has dbcreator and securityadmin permissions) is able to execute this stored procedure.

[ChristophHannappel]

Hey guys,

in addition, we should consider the size of the content databases when upgrading. We have to make sure, large content databases will not be upgraded on the same server.

I don't know its really true, but in my experience the upgrade time depends on the amount of SPWeb Objects inside the database. At least it feels like it - so maybe the SPSite and SPWeb count could help.
Alternatively the used Quota per SPSite ?