Azure Data Migration Service
The past few days, I have been helping a colleague in charge of a data migration project. They are using the Azure Data Migration Service to move a number of MySQL databases to Azure MySQL via offline migration.
Through the Portal UI, it is easy to set up a migration from 1 database server to Azure, selecting multiple databases in a single Migration Task. However, when you have a large number of servers, all with a multitude of databases. To minimise downtime, then we need to turn to programmatic solutions.
Microsoft supply a pretty comprehensive guide for Migrating databases via PowerShell
The only issue is the script examples only work correctly for a single database in the migration project. So we needed to modify some areas to work with multiple databases.
The Table Map is the Key
I spent a bit of time investigating the whole completed script and concluded the Table Map vital to the migration project.
The Table Map is a simple dictionary of string pairs used to link source and destination database tables. Even though the Table Map contains the database name for MySQL, there must be a table map for each database in the migration project.
Script Modifications
Extract the list of table names from the target database
The first place in the script we modified was where the source and destination table names are retrieved.
The selectedDatabase element already takes an array of database names, so no change is needed.
$getTablesTaskProperties.input.selectedDatabases = @($TargetDatabaseName);
The issue comes from the $getTableTaskName variable expects a string rather than an array.
$getTableTaskName = "$($TargetDatabaseName)GetUserTables"
So as a quick test, we modified the string to pull the first element from the array. Crude, but it worked.
$getTableTaskName = "$($TargetDatabaseName[0])GetUserTables"
We could then modified the command to something more legible and meaningful.
$getTableTaskName = "TargetDatabaseGetUserTables"
We repeated this modification for the Task to retrieve Source Tables, resulting in two objects containing the required table list.
Build table mapping based on user configuration
This section of the script is where things got a bit tricky. All the calls here assuming a single database rather than an array. It also manipulates database names during checks to ensure source and destination databases contain the same tables, even when the source and destination database names are different.
To simplify our script, we took advantage of our database names being unchanged via the migration process and removed some of the PowerShell gymnastics.
We decided to create an array of Table Maps that could then be used in the final stages of the script. To do this we iterated through the TargetTables output for each Database.
$offlineSelectedDatabases = @()
foreach ($TargetDatabase in $TargetDatabaseName)
{
$targetTables = $getTargetTablesTask.Output.DatabasesToTables.$TargetDatabase;
$tableMap = New-Object 'system.collections.generic.dictionary[string,string]';
foreach ($targetTable in $targetTables)
{
$tableMap.Add($targetTable.Name, $targetTable.Name);
}
$offlineSelectedDatabase = [ordered]@{
"name" = $TargetDatabase;
"targetDatabaseName" = $TargetDatabase;
"tableMap" = $tableMap;
};
$offlineSelectedDatabases = @($offlineSelectedDatabases + $offlineSelectedDatabase)
}
Create and configure the migration task inputs
After building individual table maps for each database we were importing, we needed to create the Data Migration project.
The example project again worked perfectly for a single database. We needed to modify the targetDatabases object to pass in the array of Table maps, rather than a single table.
Once completed, we successfully migrated six databases from a single server in a single project.
$offlineMigTaskProperties = @{
"input" = @{
"sourceConnectionInfo" = $null;
"targetConnectionInfo" = $null;
"selectedDatabases" = @()
"optionalAgentSettings" = @{
"EnableCacheBatchesInMemory" = $true;
"DisableIncrementalRowStatusUpdates" = $true;
};
"startedOn" = $null;
};
"taskType" = "Migrate.MySql.AzureDbForMySql";
};
# Set connection info and the database mapping
$offlineMigTaskProperties.input.sourceConnectionInfo = $sourceConnInfo;
$offlineMigTaskProperties.input.targetConnectionInfo = $targetConnInfo;
$offlineMigTaskProperties.input.selectedDatabases = @($offlineSelectedDatabases);
$offlineMigTaskProperties.input.startedOn = [System.DateTimeOffset]::UtcNow.ToString("O");