Migrating Thousands Databases To Azure ONLINE? Here Is How We Got It Done - Part3: Get It Going (B)

Part1(A), Part1(B) Part2, Part3(A), Part3(C), Part4(A), Part4(B)

Step 8, Automatically Create 100 DMS Activities

In the same "Please select an action to perform" window, select CreateNewDMSTask to actually start database migration by creating all (100) configured DMS activities. Go back to the PS console, where the script has paused, to double check the activity names, then type 'Y' to continue. Below is the part of the code creating the DMS activites:

$SourceConnInfo = New-AzDMSConnInfo -ServerType SQL `
                -DataSource $SourceServerName `
                -AuthType WindowsAuthentication `
                -TrustServerCertificate:$true
    
$TargetConnInfo = New-AzDMSConnInfo -ServerType SQLMI -MiResourceId $TargetServerName
    
$backupFileSharePath = $MigrationIDArray.BackupFileShare
$backupFileShare = New-AzDMSFileShare -Path $backupFileSharePath -Credential $backupCred
    
$DBList = $SourceDBList | Where-Object MigrationId -eq $MigrationId
    
$SelectedDbs = @()
foreach ($SourceDBName in $DBList.SourceDBName) {
  $TargetDB = ($DBList | Where-Object SourceDBName -eq $SourceDBName).TargetDBName
  Write-Verbose -Message "The target database name is '$TargetDB'"
  $backupFileSharePathSubFolder = $backupFileSharePath + "\" + $SourceDBName
  $backupFileSharePathSubFolder = New-AzDMSFileShare -Path $backupFileSharePathSubFolder -Credential $backupCred
    
  $selectedDbs += New-AzDMSSelectedDB -MigrateSqlServerSqlDbMi `
                    -Name $SourceDBName `
                    -TargetDatabaseName $TargetDB `
                    -BackupFileShare $backupFileSharePathSubFolder
            }
  $CreationTimeSTamp = Get-Date
  Write-Output "`nINFO: Creating DMS task(Type:'$TaskType') '$DMSTaskName' at '$CreationTimeStamp' with the migration id '$MigrationId' for the following databases:"
  $DBList.SourceDBName
  try {
                $Params = @{
                    'TaskType'                = $TaskType
                    'ResourceGroupName'       = $ResourceGroupName
                    'ServiceName'             = $DMSName
                    'ProjectName'             = $DMSProjectName
                    'TaskName'                = $DMStaskName
                    'SourceConnection'        = $sourceConnInfo
                    'SourceCred'              = $sourceCred
                    'TargetConnection'        = $targetConnInfo
                    'TargetCred'              = $targetCred
                    'SelectedDatabase'        = $selectedDbs
                    'BackupFileShare'         = $backupFileShare
                    'AzureActiveDirectoryApp' = $app
                    'StorageResourceId'       = $storageResourceId
                    'ErrorAction'             = 'Continue'
                    'ErrorVariable'           = 'MyAzDMSErrorMsg'
                   }
  $NewTask = New-AzDMSTask @Params
  Add-Content -Path $LogFile -Value "$(get-date): Error: $MyAzDMSErrorMsg"
}
catch {
  Write-Error -Message "Failed to create the task '$DMSTaskName'" -ErrorAction Continue -ErrorVariable MyAzDMSErrorMsg
  Add-Content -Path $LogFile -Value "$(get-date): Error: $MyAzDMSErrorMsg"
  Write-Output $_
  Add-Content -Path $LogFile -Value "$(get-date): Error: $_"
  exit
}

This part of the PowerShell code is too complex to put it in a Start-Job ScriptBlock and it normally takes 3 minutes or more to create one DMS activity. To speed up the process of creating DMS activities, we can run the MyAzDMS.ps1 with all the required parameter values in multiple PowerShell console windows to achieve parallel operations. Here is how I usually do:

In the PS console windows #1, run MyAzDMS.ps1 like this:

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -StartMigrationId 1 -EndMigrationId 20 -Action CreateNewDMSTask

After confirmation (Type 'Y'), the execution will create the first batch of DMS activities named from MyDMS1-Project1-Activity1 to MyDMS20-Project1-Activity1

In the PS console windows #2, run MyAzDMS.ps1 like this:

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -StartMigrationId 21 -EndMigrationId 40 -Action CreateNewDMSTask

After confirmation (Type 'Y'), the execution will create the second batch of DMS activities named from MyDMS21-Project1-Activity1 to MyDMS40-Project1-Activity1.

Then in the PS console window #3,#4 and #5, run the following commands accordingly:

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -StartMigrationId 41 -EndMigrationId 60 -Action CreateNewDMSTask

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -StartMigrationId 61 -EndMigrationId 80 -Action CreateNewDMSTask

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -StartMigrationId 81 -EndMigrationId 100 -Action CreateNewDMSTask

The five PS command lines mentioned above can be simplified as below because $ConfigId, $StartMigrationId and $EndMigrationId are all positioned parameters:

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 20 -Action CreateNewDMSTask
.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 21 40 -Action CreateNewDMSTask
.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 41 60 -Action CreateNewDMSTask
.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 61 80 -Action CreateNewDMSTask
.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 81 100 -Action CreateNewDMSTask

Step 9, Monitor DMS

1. DMS Instances and Projects

To check the availability of all 100 DMS instances and projects, run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action CheckDMSInstanceAndProject

Or to check the individual DMS instances and projects, for example, MyDMS1, MyDMS6, MyDMS8 and MyDMS100, run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action CheckDMSInstanceAndProject

The script output shows the state of current DMS instances and reports if the DMS project has been defined in those online DMS instances.

The commands above not only show information on the PS console screen, but also update the following three columns in TaskConfig table (for example, TaskConfig_Prod01) at the same time so that you can query the table later to generate reports showing the DMS status.

UpdateTimeStamp
DMSState
DMSProjectState

2. DMS Activities

To check the runtime status of all 100 DMS activities, run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action CheckDMSTaskState

Or to check the status of individual DMS activities, for example, the following ones:

• MyDMS1-Project1-Activity1

• MyDMS6-Project1-Activity

• MyDMS8-Project1-Activity

• MyDMS100-Project1-Activity

run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action CheckDMSTaskState

The commands also update the following four columns in TaskConfig table (for example, TaskConfig_Prod01)

UpdateTimeStamp
DMSTaskState
DMSTaskErrors
DMSTaskErrorDetails

and thirteen (13) columns in MigGrp table (MigGrp_Prod01) at the same time so that you can query the tables later to generate reports showing the DMS activity status.

UpdateTimeStamp
MigrationState
StartON
LastRestoredFile
BackupStartDate
ErrorCode
ErrorMessage
FirstLsn
LastLsn
IsFullBackupRestored
SAContainerName
NumberOfActiveBackupSets
NumberOfFilesPendingRestoration

3. Target Database State

To check the state of all (for example, 305) the target databases on the target SQL Managed Instances run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action CheckTargetDBState

Or to check the target databases that are being migrated by the following DMS activities:

• MyDMS1-Project1-Activity1

• MyDMS6-Project1-Activity

• MyDMS8-Project1-Activity

• MyDMS100-Project1-Activity

run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action CheckTargetDBState

The commands above not only show information on the PS console screen, but also update the column TargetDBState in MigGrp table (MigGrp_Prod01) during its execution.

4. Target SQLMI Storage Capacity

The DMS activity doesn't verify if the target SQLMI has enough storage capacity for restoring all the databases before it starts . The DMS task fails completely if the target SQLMI run out of disk space. To check the storage capacity on all the target SQLMIs, run:

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action CheckTargetSQLServerDiskSpace

Or to check the SQLMIs used by some individual activities.

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action CheckTargetSQLServerDiskSpace

This command line doesn't update any migration tables, but it show warning message on PS console screen it show which SQlMIs having less than 15% (configurable in Config.txt file) free disk space.

5. Backup Files in Azure Blob Containers

To verify how many backup files having been uploaded to the target storage accounts in the blob containers that were automatically created by the DMS activities. run :

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action ShowDMSSABlobs

Or to only check the containers used by some individual activities.

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action ShowDMSSABlobs

Note: the backup files show up in the container only after the upload has completely finished.

6. Summary of Migration State

There are two ways to get this information.

First, run T-SQL queries against MigGrp table, for example, MigGrp_Prod01:

declare @TimeStamp datetime = (select dateadd(mi,-30,getdate()))
select a.MigrationState, count(*) as Num_of_Databases, sum(NumberOfActiveBackupSets) as Total_Number_of_Active_Backup_Sets, sum(NumberOfFilesPendingRestoration) as Total_Number_Of_Files_Pending_Restoration 
from (
select (case 
	--when DMSTaskState = '' Then N'TASK_NOT_STARTED'
	--when DMSTaskState != 'Running' and DMSTaskState != '' and DMSTaskState != 'Succeeded' Then N'TASK_FAILED_OR_STARTING'
	when MigrationState = '' and ErrorCode is not NULL and ErrorCode !='' Then N'DATABASE MIGRATION FAILED'
	when MigrationState = '' and (ErrorCode is NULL or ErrorCode ='') Then N'TASK_IN_VALIDATION'
	when MigrationState = 'FULL_BACKUP_UPLOADING'  Then N'FULL_BACKUP_UPLOADING'
	when MigrationState = 'LOG_FILES_UPLOADING' and (LastREstoredFile like '%_20210413_1745.trn' or LastREstoredFile like '%_20210413_1746.trn') Then N'READY_FOR_CUTOVER'
	when MigrationState = 'LOG_FILES_UPLOADING' and LastRestoredFile='' Then N'FULL_BACKUP_RESTORING'
	when MigrationState = 'LOG_FILES_UPLOADING' and LastRestoredFile like '%.bak' Then N'FULL_BACKUP_JUST_RESTORED'
	when MigrationState = 'LOG_FILES_UPLOADING' and LastREstoredFile like '%.trn' and BackupStartDate > @TimeStamp Then N'LOG_FILES_RESTORING_ONTIME'
	when MigrationState = 'LOG_FILES_UPLOADING' and LastREstoredFile like '%.trn' and BackupStartDate < @TimeStamp Then N'LOG_FILES_RESTORING_LAGGING'
		else MigrationState
		end) as MigrationState, NumberOfActiveBackupSets, NumberOfFilesPendingRestoration
from MigGrp_W61Prod as MG left join TaskConfig_W61Prod as TC on MG.migrationid=TC.MigrationId where MG.migrationId is not null and TC.IsActiveForMigration = 'Y'
) as a 
group by a.MigrationState

The query output looks like below:

Another way to get similar report is to run :

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action ShowMigrationStateSummary

Or to only check the migrations handled by some individual activities.

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action ShowMigrationStateSummary

Step 10, Cutover

This is the last step to complete the online database migration and the only step needs a short database down time. Technically, a migration cutover can be performed any time after the full database backup is restored on the target SQL Managed Instance, but usually need the following the actions to take place in sequence during the outage window:

1. Stop all the incoming traffic to the source databases

2. Take the tail-log backup with a special backup file name which is defined in Config.txt file, for example, LastLogBackupFileNameSuffix = _LastLog01.trn. The code handling cutover in MyAzDMS.ps1 verify and ensure the last restored log backup is actually the final one before issuing migration cutover on the target database.

3. Make the last backup file available in the SMB network share, and then wait until this final transaction log backup is restored.

4. Start cutover using the following command lines:

.\MyAzDMS.ps1 -ConfigFile .\config.txt 1 1 100 -Action CutoverCheckingLastLog

Or to only perform cutover on the databases handled by some individual activities.

.\MyAzDMS.ps1 -ConfigFile .\config.txt -ConfigId 1 -MigrationId 1,6,8,100 -Action CutoverCheckingLastLog

Important:

After the cutover, depending on the size of the data, the availability of SQL Managed Instance with Business Critical service tier only can take significantly longer than General Purpose as three secondary replicas have to be seeded for AlwaysOn High Availability group.

Finally, when the database migration status shows Completed, you can test to connect your applications to the new target SQL Managed Instance.