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

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

Step 1, Get the Codes

On your DMS console machine, cloning the following git repository in any folder where you have write permission.

(git repository currently under upgrade, email me if you need the codes now)

Step 2, Review and Modify Config.txt

Config.txt contains all the parameter/value pairs used by the main PowerShell script. You can review and edit any parameter values in this file without touching the code in the PS script or typing them on the command line every time you run the script. This file is optional, but strongly recommended to use unless you would like to modify the PS script when it's needed to change the parameter values type the new value on the PS command line to override the default parameter values.

If the path to Config.txt file is not specified on the command line, a warning message along with a list of default parameter/value pair show in the PS windows like below:

The PS script output also list any parameter/value pairs you may have entered on the command line. Now you can choose to either accept the default parameter values and continue by typing 'Y' or cancel the current PS execution by pressing any key.

Here it the whole content of the Config.txt file. Each and every parameter/value pairs here have their corresponding parameter/value pairs in MyAzDMS.ps1

#The subscription where you create and run your DMS instances
SubscriptionName = Your_Azue_Script_Name
#The Azure resource group name for DMS
ResourceGroupName = Your_Azure_RG_Name
#The Azure region where you create DMS instances
AzLocation = eastus2

#The SQL instance name where you run your DMS config database
ConfigSQLInstanceName = Your_SQL_Server_name
#The database where you created DMSConfig, TaskConfig_XXX and 
#MigGrp_XXX
ConfigSQLDatabaseName = Your_DMS_Database_Name

#The ConfigId in DMSConfig table to specify the migration group
ConfigId = 1
#The PS script can work on a range of Migration IDs, the following two
#parameters specifying the start and end Migration ID of the range
StartMigrationId = 1
EndMigrationId =100

#The three parameters below combine with the column values in 
#DMSConfig table provide naming convention for DMS objects. 
#For example, if a record in DMSConfig table has column values:
#ServiceId = 1
#ProjectId = 8
#ActivityId = 6
#Then the corresponding DMS instance name will be: "MyDMS1", the DMS #project name will be "MyDMS1-Project8" and the DMS activity name 
#will be "MyDMS1-Project8-Activity6"
DMSNamePrefix = MyDMS
DMSProjectNamePrefix = Project
DMSActivityNamePrefix = Activity

#Specify the pause time (seconds) between DMS activity creation to 
#avoid system being overloaded
SleepDuration = 1

#If it's true, the PS script pause before starting the major action 
#so that you have chance to review and confirm
ConfirmAction = true
#Tell if PS script should check the current PS runtime version
CheckPSVersion = false
#Tell if PS script should check the existence of required PS modules 
CheckPSModule = false
#Tell if PS script check and confirm the current Azure login context
CheckAzlogin = true

#Tell if PS script should check the status of DMS instances and 
#projects before taking any major actions. Once your DMS 
#environment is stabilized,turn this off (false) to speed up 
#the execution of the PS script
CheckDMSInstanceAndProjectState = false

#Be careful with this variable, this is a protection to prevent you
#from accidently deleting a target database. If it's on (true), 
#the PS script asks for confirmation each time it deletes a 
#target database
ForceTargetDBDeletion = true

#The Azure resource group name of the VNet where you create 
#DMS instances
DMSVnetRGName = Your_DMS_VNet_RG_Name
#The DMS virtual network name
DMSVNetName = Your_DMS_VNet_Name
#The DMS subnet name
DMSVSubnetName = Your_DMS_Subnet_Name

#The PS script can work on a range of DMS project IDs, the 
#following two parameters specifying the start and end project 
#ID of the range
StartProjectId = 1
EndProjectId = 20

#Specify the naming convention of the last SQL Server transaction 
#log file
LastLogBackupFileNameSuffix = _LastLog01.trn
#LastLogBackupFileNameSuffix = _20210413_1745.trn

#The PS script has function to check the disk space usage on the 
#target SQL Managed Instances. This parameter specify the free 
#space percentage. The PS script reports warning message if 
#it detects the free space islower than this value
TargetSQLFreeDiskSpaceWarningPercentage = 15

#Used for setting PS console color
#"$host.ui.RawUI.ForegroundColor = $DefaultForegroundColor"
DefaultForegroundColor = Gray

#Be careful with this variable, this is a protection to prevent 
#you from accidently deleting an old existing database on the 
#target SQL Server If the database was create NewDBAgeHours ago, 
#it is considered as an old #existing database not allowed be 
#deleted by the DMS PS script
NewDBAgeHours = 24

#The parameter is used to construct the REST API URL which is used 
#by an API call to complete database restoration 
CompleteRestoreAPIVeraion = 2017-03-01-preview
#If it's on (true), the PS script asks for confirmation before posting 
#the REST API call
ConfirmAPIRestore = false

#Specify which DMS activity Id to use when redoing the DMS 
#activities. Use any number (ID) not currently being used to 
#avoid naming conflicts
RedoMigrationActivityId = 2

#Specify the minimum version of Az module required by 
#the PS script
AzModuleVersion = 3.5.0
#Specify the minimum version of Az.DataMigration module required by 
#the PS script
AzDataMigrationModuleVersion = 0.7.4
#Specify the minimum version of SQLServer module required by 
#the PS script
SQLServerVersion = 21.1.18218

#Specify default PowerShell error action preference
ErrorActionPreference = Stop

#The PS script writes lots of log records to the log file and it 
#fails if the path to the log file doesn't exist
LogFile = C:\users\ling\DMS\Log\MyAzDMS.log

Step 3, Login Azure and Select Subscription

In a PowerShell console, change directory to where you cloned the codes(For example, "C:\Users\ling\DMS2"), then run MyAzDMS.ps1 like this:

PS C:\Users\ling\DMS2> .\MyAzDMS.ps1 -ConfigFile .\config.txt

The PS script will try to sign in Azure if you haven't done so and ask you to select your target subscription from a list of subscription names if it cannot find the parameter in Config.txt. For best practice, you should always set the SubscriptionName parameter in Config.txt.

Step 4, Select Migration Group (ConfigId)

If the PS script cannot find the ConfigId parameter in Config.txt, it pops up a window, named "Please select one of the DMS migration groups from the list", with a grid showing all the rows in DMSConfig table. Here you can pick the row representing the migration group you are working on, then click OK to continue to the next step.

Step 5. Select Migration IDs

The next popup window "Please select the DMS activities from the list" shows a name list of all the DMS objects (DMS Instances, Projects and Activities) basing on the row values in TaskConfig_Prod01 table ordered by the MigrationId. You can select all or some of the MigrationIDs for the next step. Let's select all rows and click OK to continue.

Step 6, Automatically Deploy 100 DMS Instances In Parallel

The next "Please select an action to perform" window shows all the actions you can take on all the migration IDs you picked in the previous step. Obviously, we need first deploy DMS instances in the target Azure subscription. So select the action name CreateDMSInstance, then click OK to continue

Go back to the PowerShell console window in which you run the script, you will see the script has paused the execution and shows a list of 100 (because we selected all rows in Step 5) DMS instance names proposed to create. Next, if you press 'Y', the script will go ahead create 100 DMS instances one by one in the Azure subnet and in the resource group specified in Config.txt file. It skips creating the DMS instances if they already exist.

It takes around 30 minutes to create a new DMS instance, but the script uses Start-Job cmdlet to send all the New-AzDms commands to run simultaneously in the background on the local computer without waiting for them to finish the deployment. So when you soon come back to "Please select an action to perform" window, that doesn't mean all 100 DMS instances have been deployed. Below is the corresponding code in the MyAzDMS.ps1:

$JobName = "Create-" + $DMSName
Start-Job -Name $JobName -ScriptBlock {
   New-AzDms -ResourceGroupName $Using:ResourceGroupName `
      -ServiceName $Using:DMSName `
      -Location $Using:AzLocation `
      -Sku Premium_4vCores `
      -VirtualSubnetId $Using:DMSVSubnet.Id -ErrorVariable MyAzDMSErrorMsg
   Add-Content -Path $LogFile -Value "$(get-date): Error:$MyAzDMSErrorMsg"
}

Select action name CheckDMSInstanceAndProject to verify the status of the Azure DMS instances and DMS projects which will be created next. Back to the PS console window where you start the script, you should see the output like this:

And at the end of the output, you can the status summary:

Repeat the action CheckDMSInstanceAndProject every a few minutes and wait until all the DMS instances become online (state is Succeeded, see below screenshot).

And at the bottom:

Step 7, Automatically Deploy 100 DMS Projects In Parallel

Select the action name CreateDMSProject in "Please select an action to perform" window to create all 100 projects (one in each DMS instance). The script will pause in the PS console window asking for the confirmation. Type 'Y' to continue if the project names look OK. Below is the piece of code defining the project:

$JobName = "Create-" + $DMSProjectName
Start-Job -Name $JobName -ScriptBlock { 
    New-AzDataMigrationProject -ResourceGroupName $Using:ResourceGroupName     
         -ServiceName $Using:DMSName `
         -ProjectName $Using:DMSprojectName `
         -Location $Using:AzLocation `
         -SourceType SQL `
         -TargetType SQLMI -ErrorVariable MyAzDMSErrorMsg
  Add-Content -Path $LogFile -Value "$(get-date): Error: $MyAzDMSErrorMsg" 
   }

Back to "Please select an action to perform" window and repeat the action CheckDMSInstanceAndProject until you all the DMS projects are created successfully:

And at the bottom: