General Purpose to Business Critical Azure SQL database upgrade

by Contributed | Aug 18, 2024 | Technology

This article is contributed. See the original author and article here.

Recently, we faced a requirement to upgrade large number of Azure SQL databases from general-purpose to business-critical.

As you’re aware, this scaling-up operation can be executed via PowerShell, CLI, or the Azure portal and follow the guidance mentioned here – Failover groups overview & best practices – Azure SQL Managed Instance | Microsoft Learn

Given the need to perform this task across a large number of databases, individually running commands for each server is not practical. Hence, I have created a PowerShell script to facilitate such extensive migrations.

Important consideration:

# This script performs an upgrade of Azure SQL databases to a specified SKU.

# The script also handles geo-replicated databases by upgrading the secondary first, then the primary, and finally any other databases without replication links.

# The script logs the progress and outcome of each database upgrade to the console and a log file.

# Disclaimer: This script is provided as-is, without any warranty or support. Use it at your own risk.

# Before running this script, make sure to test it in a non-production environment and review the impact of the upgrade on your databases and applications.

# The script may take a long time to complete, depending on the number and size of the databases to be upgraded.

# The script may incur additional charges for the upgraded databases, depending on the target SKU and the duration of the upgrade process.

# The script requires the Az PowerShell module and the appropriate permissions to access and modify the Azure SQL databases.

 
# Define the list of database information
$DatabaseInfoList = @(
  #@{ DatabaseName = '{DatabaseName}'; PartnerResourceGroupName = '{PartnerResourcegroupName}'; ServerName = '{ServerName}'  ; ResourceGroupName = '{ResourceGroupName}'; RequestedServiceObjectiveName =  '{SLODetails}'; subscriptionID = '{SubscriptionID}' }
   )

# Define the script block that performs the update
$ScriptBlock = {
    param (
        $DatabaseInfo
    )

    Set-AzContext -subscriptionId $DatabaseInfo.subscriptionID

        ###store output in txt file
        $OutputFilePath = "C:temp$($DatabaseInfo.DatabaseName)_$($env:USERNAME)_$($Job.Id)_Output.txt"
        $OutputCapture = @()
        $OutputCapture += "Database: $($DatabaseInfo.DatabaseName)"

    
    $ReplicationLink = Get-AzSqlDatabaseReplicationLink -DatabaseName $DatabaseInfo.DatabaseName -PartnerResourceGroupName $DatabaseInfo.PartnerResourceGroupName -ServerName $DatabaseInfo.ServerName -ResourceGroupName $DatabaseInfo.ResourceGroupName
    $PrimaryServerRole = $ReplicationLink.Role
    $PrimaryResourceGroupName = $ReplicationLink.ResourceGroupName
    $PrimaryServerName = $ReplicationLink.ServerName
    $PrimaryDatabaseName = $ReplicationLink.DatabaseName

    $PartnerRole = $ReplicationLink.PartnerRole
    $PartnerServerName = $ReplicationLink.PartnerServerName
    $PartnerDatabaseName = $ReplicationLink.PartnerDatabaseName
    $PartnerResourceGroupName = $ReplicationLink.PartnerResourceGroupName


    $UpdateSecondary = $false
    $UpdatePrimary = $false

    if ($PartnerRole -eq "Secondary" -and $PrimaryServerRole -eq "Primary") {
        $UpdateSecondary = $true
        $UpdatePrimary = $true
    }
    #For Failover Scenarios only
    elseif ($PartnerRole -eq "Primary" -and $PrimaryServerRole -eq "Secondary") {
        $UpdateSecondary = $true
        $UpdatePrimary = $true

        $PartnerRole = $ReplicationLink.Role
        $PartnerServerName = $ReplicationLink.ServerName
        $PartnerDatabaseName = $ReplicationLink.DatabaseName
        $PartnerResourceGroupName = $ReplicationLink.ResourceGroupName
        
        $PrimaryServerRole = $ReplicationLink.PartnerRole
        $PrimaryResourceGroupName = $ReplicationLink.PartnerResourceGroupName
        $PrimaryServerName = $ReplicationLink.PartnerServerName
        $PrimaryDatabaseName = $ReplicationLink.PartnerDatabaseName
    }

    Try
    {
        if ($UpdateSecondary) {
            $DatabaseProperties = Get-AzSqlDatabase -ResourceGroupName $PartnerResourceGroupName -ServerName $PartnerServerName -DatabaseName $PartnerDatabaseName
            #$DatabaseEdition = $DatabaseProperties.Edition
            $DatabaseSKU = $DatabaseProperties.RequestedServiceObjectiveName
            if ($DatabaseSKU -ne $DatabaseInfo.RequestedServiceObjectiveName)  {
                Write-host "Secondary started at $(Get-Date) of DB $UpdateSecondary"
                $OutputCapture += "Secondary started at $(Get-Date) of DB $UpdateSecondary"
               
                Set-AzSqlDatabase -ResourceGroupName $PartnerResourceGroupName -DatabaseName $PartnerDatabaseName -ServerName $PartnerServerName -Edition "BusinessCritical"  -RequestedServiceObjectiveName $DatabaseInfo.RequestedServiceObjectiveName
                Write-host "Secondary end at $(Get-Date)"
                $OutputCapture += "Secondary end at $(Get-Date)"
        
                
                # Start Track Progress
                $activities = Get-AzSqlDatabaseActivity -ResourceGroupName $PartnerResourceGroupName -ServerName $PartnerServerName -DatabaseName $PartnerDatabaseName |
                Where-Object {$_.State -eq "InProgress" -or $_.State -eq "Succeeded" -or $_.State -eq "Failed"} |  Sort-Object -Property StartTime -Descending | Select-Object -First 1

                if ($activities.Count -gt 0) {
                    Write-Host "Operations in progress or completed for $($PartnerDatabaseName):"
                    $OutputCapture += "Operations in progress or completed for $($PartnerDatabaseName):"
                    foreach ($activity in $activities) {
                    Write-Host "Activity Start Time: $($activity.StartTime) , Activity Estimated Completed Time: $($activity.EstimatedCompletionTime) , Activity ID: $($activity.OperationId), Server Name: $($activity.ServerName), Database Name: $($activity.DatabaseName), Status: $($activity.State), Percent Complete: $($activity.PercentComplete)%, Description: $($activity.Description)"
                    $OutputCapture += "Activity Start Time: $($activity.StartTime) , Activity Estimated Completed Time: $($activity.EstimatedCompletionTime) , Activity ID: $($activity.OperationId), Server Name: $($activity.ServerName), Database Name: $($activity.DatabaseName), Status: $($activity.State), Percent Complete: $($activity.PercentComplete)%, Description: $($activity.Description)"
                    }
                    Write-Host  "$PartnerDatabaseName Upgrade Successfully Completed!"
                    $OutputCapture += "$PartnerDatabaseName Upgrade Successfully Completed!"
                } else {
                    Write-Host "No operations in progress or completed for $($PartnerDatabaseName)"
                    $OutputCapture += "No operations in progress or completed for $($PartnerDatabaseName)"
                }
                # End Track Progress
               # 
            }
            else {
                Write-host "Database $PartnerDatabaseName is already upgraded."
                $OutputCapture += "Database $PartnerDatabaseName is already upgraded."
            }
        }

        if ($UpdatePrimary) {
            $DatabaseProperties = Get-AzSqlDatabase -ResourceGroupName $PrimaryResourceGroupName -ServerName $PrimaryServerName -DatabaseName $PrimaryDatabaseName
           # $DatabaseEdition = $DatabaseProperties.Edition
            $DatabaseSKU = $DatabaseProperties.RequestedServiceObjectiveName
            if ($DatabaseSKU -ne $DatabaseInfo.RequestedServiceObjectiveName){
            Write-host "Primary started at $(Get-Date) of DB $UpdatePrimary"
            $OutputCapture += "Primary started at $(Get-Date) of DB $UpdatePrimary"
            Set-AzSqlDatabase -ResourceGroupName $PrimaryResourceGroupName -DatabaseName $PrimaryDatabaseName -ServerName $PrimaryServerName -Edition "BusinessCritical"  -RequestedServiceObjectiveName $DatabaseInfo.RequestedServiceObjectiveName
            Write-host "Primary end at $(Get-Date)" 
            $OutputCapture += "Primary end at $(Get-Date)"
            

            # Start Track Progress
            $activities = Get-AzSqlDatabaseActivity -ResourceGroupName $PrimaryResourceGroupName -ServerName $PrimaryServerName -DatabaseName $PrimaryDatabaseName |
            Where-Object {$_.State -eq "InProgress" -or $_.State -eq "Succeeded" -or $_.State -eq "Failed"} |  Sort-Object -Property StartTime -Descending | Select-Object -First 1

            if ($activities.Count -gt 0) {
                Write-Host "Operations in progress or completed for $($PrimaryDatabaseName):"
                $OutputCapture += "Operations in progress or completed for $($PrimaryDatabaseName):"
                foreach ($activity in $activities) {
                Write-Host "Activity Start Time: $($activity.StartTime) , Activity Estimated Completed Time: $($activity.EstimatedCompletionTime) , Activity ID: $($activity.OperationId), Server Name: $($activity.ServerName), Database Name: $($activity.DatabaseName), Status: $($activity.State), Percent Complete: $($activity.PercentComplete)%, Description: $($activity.Description)"
                $OutputCapture += "Activity Start Time: $($activity.StartTime) , Activity Estimated Completed Time: $($activity.EstimatedCompletionTime) , Activity ID: $($activity.OperationId), Server Name: $($activity.ServerName), Database Name: $($activity.DatabaseName), Status: $($activity.State), Percent Complete: $($activity.PercentComplete)%, Description: $($activity.Description)"
                }
                Write-Host  "$PrimaryDatabaseName Upgrade Successfully Completed!" 
                $OutputCapture += "$PrimaryDatabaseName Upgrade Successfully Completed!"
            } else {
                Write-Host "No operations in progress or completed for $($PrimaryDatabaseName)"
                $OutputCapture += "No operations in progress or completed for $($PrimaryDatabaseName)"
            }
            # End Track Progress
           #           
            }
            else {
                Write-host "Database $PrimaryDatabaseName is already upgraded."
                $OutputCapture += "Database $PrimaryDatabaseName is already upgraded."
            }
        }
        
        if (!$UpdateSecondary -and !$UpdatePrimary) {
            $DatabaseProperties = Get-AzSqlDatabase -ResourceGroupName $DatabaseInfo.ResourceGroupName -ServerName $DatabaseInfo.ServerName -DatabaseName $DatabaseInfo.DatabaseName
            # $DatabaseEdition = $DatabaseProperties.Edition
             $DatabaseSKU = $DatabaseProperties.RequestedServiceObjectiveName
        If ($DatabaseSKU -ne $DatabaseInfo.RequestedServiceObjectiveName)  {
            Write-Host "No Replica Found."
            $OutputCapture += "No Replica Found."
            Write-host "Upgrade started at $(Get-Date)"
            $OutputCapture += "Upgrade started at $(Get-Date)"
            Set-AzSqlDatabase -ResourceGroupName $DatabaseInfo.ResourceGroupName -DatabaseName $DatabaseInfo.DatabaseName -ServerName $DatabaseInfo.ServerName -Edition "BusinessCritical"  -RequestedServiceObjectiveName $DatabaseInfo.RequestedServiceObjectiveName
            Write-host "Upgrade completed at $(Get-Date)"
            $OutputCapture += "Upgrade completed at $(Get-Date)"

            # Start Track Progress
            $activities = Get-AzSqlDatabaseActivity -ResourceGroupName $DatabaseInfo.ResourceGroupName -ServerName $DatabaseInfo.ServerName -DatabaseName $DatabaseInfo.DatabaseName |
            Where-Object {$_.State -eq "InProgress" -or $_.State -eq "Succeeded" -or $_.State -eq "Failed"} |  Sort-Object -Property StartTime -Descending | Select-Object -First 1

            if ($activities.Count -gt 0) {
                Write-Host "Operations in progress or completed for $($DatabaseInfo.DatabaseName):"
                $OutputCapture += "Operations in progress or completed for $($DatabaseInfo.DatabaseName):"
                foreach ($activity in $activities) {
                Write-Host "Activity Start Time: $($activity.StartTime) , Activity Estimated Completed Time: $($activity.EstimatedCompletionTime) , Activity ID: $($activity.OperationId), Server Name: $($activity.ServerName), Database Name: $($activity.DatabaseName), Status: $($activity.State), Percent Complete: $($activity.PercentComplete)%, Description: $($activity.Description)"
                $OutputCapture += "Activity Start Time: $($activity.StartTime) , Activity Estimated Completed Time: $($activity.EstimatedCompletionTime) , Activity ID: $($activity.OperationId), Server Name: $($activity.ServerName), Database Name: $($activity.DatabaseName), Status: $($activity.State), Percent Complete: $($activity.PercentComplete)%, Description: $($activity.Description)"
                }
                Write-Host  " "$DatabaseInfo.DatabaseName" Upgrade Successfully Completed!"
                $OutputCapture += "$($DatabaseInfo.DatabaseName) Upgrade Successfully Completed!"
            } else {
                Write-Host "No operations in progress or completed for $($DatabaseInfo.DatabaseName)"
                $OutputCapture += "No operations in progress or completed for $($DatabaseInfo.DatabaseName)"
            }
            # End Track Progress
           # Write-Host  " "$DatabaseInfo.DatabaseName" Upgrade Successfully Completed!"
        }
        else {
            Write-host "Database "$DatabaseInfo.DatabaseName" is already upgraded."
            $OutputCapture += "Database $($DatabaseInfo.DatabaseName) is already upgraded."
        }
        $OutputCapture += "Secondary started at $(Get-Date) of DB $UpdateSecondary"
    }
    }
    Catch
    {
        # Catch any error
        Write-Output "Error occurred: $_"
        $OutputCapture += "Error occurred: $_"
    }
    Finally
    {
        Write-Host "Upgrade Successfully Completed!"
        $OutputCapture += "Upgrade Successfully Completed!"
            # Output the captured messages to the file
            $OutputCapture | Out-File -FilePath $OutputFilePath
    }
  
}

# Loop through each database and start a background job
foreach ($DatabaseInfo in $DatabaseInfoList) {
    Start-Job -ScriptBlock $ScriptBlock -ArgumentList $DatabaseInfo
}

# Wait for all background jobs to complete
Get-Job | Wait-Job

# Retrieve and display job results
#Get-Job | Receive-Job
Get-Job | ForEach-Object {
    $Job = $_
    $OutputFilePath = "C:temp$($Job.Id)_Output.txt"
    Receive-Job -Job $Job | Out-File -FilePath $OutputFilePath  # Append job output to the text file
}

# Clean up background jobs
Get-Job | Remove-Job -Force
write-host "Execution Completed successfully."
$OutputCapture += "Execution Completed successfully."