Migrate MySQL to Azure Database for MySQL offline with PowerShell & Azure Database Migration Service
In this article, you migrate a MySQL database restored to an on-premises instance to Azure Database for MySQL by using the offline migration capability of Azure Database Migration Service through Microsoft Azure PowerShell. The article documents a collection of PowerShell scripts which can be executed in sequence to perform the offline migration of MySQL database to Azure. You can download the complete PowerShell script described in this tutorial from our GitHub repository.
Note
Currently it is not possible to run complete database migration using the Az.DataMigration module. In the meantime, the sample PowerShell script is provided "as-is" that uses the DMS REST API and allows you to automate migration. This script will be modified or deprecated, once official support is added in the Az.DataMigration module and Azure CLI.
Note
Amazon Relational Database Service (RDS) for MySQL and Amazon Aurora (MySQL-based) are also supported as sources for migration.
Important
For online migrations, you can use open-source tools such as MyDumper/MyLoader with data-in replication.
The article helps to automate the scenario where source and target database names can be same or different and as part of migration either all or few of the tables in the target database need to be migrated which have the same name and table structure. Although the articles assumes the source to be a MySQL database instance and target to be Azure Database for MySQL, it can be used to migrate from one Azure Database for MySQL to another just by changing the source server name and credentials. Also, migration from lower version MySQL servers (v5.6 and above) to higher versions is also supported.
Important
DMS preview features are available on a self-service, opt-in basis. Previews are provided "as is" and "as available," and they're excluded from the service-level agreements and limited warranty. As such, these features aren't meant for production use. For more information, see Supplemental terms of use for Microsoft Azure previews.
In this article, you learn how to:
- Migrate database schema.
- Create a resource group.
- Create an instance of the Azure Database Migration Service.
- Create a migration project in an Azure Database Migration Service instance.
- Configure the migration project to use the offline migration capability for MySQL.
- Run the migration.
Prerequisites
To complete these steps, you need:
Have an Azure account with an active subscription. Create an account for free.
Have an on-premises MySQL database with version 5.6 or above. If not, then download and install MySQL community edition 5.6 or above.
Create an instance in Azure Database for MySQL. Refer to the article Use MySQL Workbench to connect and query data for details about how to connect and create a database using the Workbench application. The Azure Database for MySQL version should be equal to or higher than the on-premises MySQL version . For example, MySQL 5.7 can migrate to Azure Database for MySQL 5.7 or upgraded to 8.
Create a Microsoft Azure Virtual Network for Azure Database Migration Service by using Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN. For more information about creating a virtual network, see the Virtual Network Documentation, and especially the quickstart articles with step-by-step details.
Note
During virtual networkNet setup, if you use ExpressRoute with network peering to Microsoft, add the Microsoft.Sql service endpoint to the subnet in which the service will be provisioned. This configuration is necessary because Azure Database Migration Service lacks internet connectivity.
Ensure that your virtual network Network Security Group rules don't block the outbound port 443 of ServiceTag for Storage and AzureMonitor. For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.
Open your Windows firewall to allow connections from Virtual Network for Azure Database Migration Service to access the source MySQL Server, which by default is TCP port 3306.
When using a firewall appliance in front of your source database(s), you may need to add firewall rules to allow connections from Virtual Network for Azure Database Migration Service to access the source database(s) for migration.
Create a server-level firewall rule or configure VNET service endpoints for target Azure Database for MySQL to allow Virtual Network for Azure Database Migration Service access to the target databases.
The source MySQL must be on supported MySQL community edition. To determine the version of MySQL instance, in the MySQL utility or MySQL Workbench, run the following command:
SELECT @@version;Azure Database for MySQL supports only InnoDB tables. To convert MyISAM tables to InnoDB, see the article Converting Tables from MyISAM to InnoDB
The user must have the privileges to read data on the source database.
The guide uses PowerShell v7.2, which can be installed as per the installation guide
Download and install following modules from the PowerShell Gallery by using Install-Module PowerShell cmdlet; be sure to open the PowerShell command window using run as an Administrator:
- Az.Resources
- Az.Network
- Az.DataMigration
Install-Module Az.Resources
Install-Module Az.Network
Install-Module Az.DataMigration
Import-Module Az.Resources
Import-Module Az.Network
Import-Module Az.DataMigration
Migrate database schema
To transfer all the database objects like table schemas, indexes and stored procedures, we need to extract schema from the source database and apply to the target database. To extract schema, you can use mysqldump with the --no-data parameter. For this you need a machine which can connect to both the source MySQL database and the target Azure Database for MySQL.
To export the schema using mysqldump, run the following command:
mysqldump -h [servername] -u [username] -p[password] --databases [db name] --no-data > [schema file path]
For example:
mysqldump -h 10.10.123.123 -u root -p --databases migtestdb --no-data > d:\migtestdb.sql
To import schema to target Azure Database for MySQL, run the following command:
mysql.exe -h [servername] -u [username] -p[password] [database]< [schema file path]
For example:
mysql.exe -h mysqlsstrgt.mysql.database.azure.com -u docadmin@mysqlsstrgt -p migtestdb < d:\migtestdb.sql
If you have foreign keys in your schema, the parallel data load during migration will be handled by the migration task. There is no need to drop foreign keys during schema migration.
If you have triggers in the database, it will enforce data integrity in the target ahead of full data migration from the source. The recommendation is to disable triggers on all the tables in the target during migration, and then enable the triggers after migration is done.
Execute the following script in MySQL Workbench on the target database to extract the drop trigger script and add trigger script.
SELECT
SchemaName,
GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery,
Concat('DELIMITER $$ \n\n', GROUP_CONCAT(AddQuery SEPARATOR '$$\n'), '$$\n\nDELIMITER ;') as AddQuery
FROM
(
SELECT
TRIGGER_SCHEMA as SchemaName,
Concat('DROP TRIGGER `', TRIGGER_NAME, "`") as DropQuery,
Concat('CREATE TRIGGER `', TRIGGER_NAME, '` ', ACTION_TIMING, ' ', EVENT_MANIPULATION,
'\nON `', EVENT_OBJECT_TABLE, '`\n' , 'FOR EACH ', ACTION_ORIENTATION, ' ',
ACTION_STATEMENT) as AddQuery
FROM
INFORMATION_SCHEMA.TRIGGERS
ORDER BY EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER ASC
) AS Queries
GROUP BY SchemaName
Run the generated drop trigger query (DropQuery column) in the result to drop triggers in the target database. The add trigger query can be saved, to be used post data migration completion.
Log in to your Microsoft Azure subscription
Use the Connect-AzAccount PowerShell command to sign in to your Azure subscription by using PowerShell, as per the directions in the article Log in with Azure PowerShell.
The following script sets the default subscription for PowerShell session post login and creates a helper logging function for formatted console logs.
[string] $SubscriptionName = "mySubscription"
$ErrorActionPreference = "Stop";
Connect-AzAccount
Set-AzContext -Subscription $SubscriptionName
$global:currentSubscriptionId = (Get-AzContext).Subscription.Id;
function LogMessage([string] $Message, [bool] $IsProcessing = $false) {
if ($IsProcessing) {
Write-Host "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss"): $Message" -ForegroundColor Yellow
}
else {
Write-Host "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss"): $Message" -ForegroundColor Green
}
}
Register the Microsoft.DataMigration resource provider
Registration of the resource provider needs to be done on each Azure subscription only once. Without the registration, you will not be able to create an instance of Azure Database Migration Service.
Register the resource provider by using the Register-AzResourceProvider command. The following script registers the resource provider required for Azure Database Migration Service
Register-AzResourceProvider -ProviderNamespace Microsoft.DataMigration
Create a resource group
An Azure resource group is a logical container into which Azure resources are deployed and managed. Create a resource group before you create any DMS resources.
Create a resource group by using the New-AzResourceGroup command.
The following example creates a resource group named myResourceGroup in the West US 2 region under the default subscription mySubscription.
# Get the details of resource group
[string] $Location = "westus2"
[string] $ResourceGroupName = "myResourceGroup"
$resourceGroup = Get-AzResourceGroup -Name $ResourceGroupName
if (-not($resourceGroup)) {
LogMessage -Message "Creating resource group $ResourceGroupName..." -IsProcessing $true
$resourceGroup = New-AzResourceGroup -Name $ResourceGroupName -Location $Location
LogMessage -Message "Created resource group - $($resourceGroup.ResourceId)."
}
else { LogMessage -Message "Resource group $ResourceGroupName exists." }
Create an instance of Azure Database Migration Service
You can create new instance of Azure Database Migration Service by using the New-AzDataMigrationService command. This command expects the following required parameters:
- Azure Resource Group name. You can use New-AzResourceGroup command to create Azure Resource group as previously shown and provide its name as a parameter.
- Service name. String that corresponds to the desired unique service name for Azure Database Migration Service
- Location. Specifies the location of the service. Specify an Azure data center location, such as West US or Southeast Asia
- Sku. This parameter corresponds to DMS Sku name. The currently supported Sku name are Standard_1vCore, Standard_2vCores, Standard_4vCores, Premium_4vCores.
- Virtual Subnet Identifier. You can use Get-AzVirtualNetworkSubnetConfig command to get the information of a subnet.
The following script expects that the myVirtualNetwork virtual network exists with a subnet named default and then creates a Database Migration Service with the name myDmService under the resource group created in Step 3 and in the same region.
# Get a reference to the DMS service - Create if not exists
[string] $VirtualNetworkName = "myVirtualNetwork"
[string] $SubnetName = "default"
[string] $ServiceName = "myDmService"
$dmsServiceResourceId = "/subscriptions/$($global:currentSubscriptionId)/resourceGroups/$ResourceGroupName/providers/Microsoft.DataMigration/services/$ServiceName"
$dmsService = Get-AzResource -ResourceId $dmsServiceResourceId -ErrorAction SilentlyContinue
# Create Azure DMS service if not existing
# Possible values for SKU currently are Standard_1vCore,Standard_2vCores,Standard_4vCores,Premium_4vCores
if (-not($dmsService)) {
$virtualNetwork = Get-AzVirtualNetwork -ResourceGroupName $ResourceGroupName -Name $VirtualNetworkName
if (-not ($virtualNetwork)) { throw "ERROR: Virtual Network $VirtualNetworkName does not exists" }
$subnet = Get-AzVirtualNetworkSubnetConfig -VirtualNetwork $virtualNetwork -Name $SubnetName
if (-not ($subnet)) { throw "ERROR: Virtual Network $VirtualNetworkName does not contains Subnet $SubnetName" }
LogMessage -Message "Creating Azure Data Migration Service $ServiceName..." -IsProcessing $true
$dmsService = New-AzDataMigrationService `
-ResourceGroupName $ResourceGroupName `
-Name $ServiceName `
-Location $resourceGroup.Location `
-Sku Premium_4vCores `
-VirtualSubnetId $Subnet.Id
$dmsService = Get-AzResource -ResourceId $dmsServiceResourceId
LogMessage -Message "Created Azure Data Migration Service - $($dmsService.ResourceId)."
}
else { LogMessage -Message "Azure Data Migration Service $ServiceName exists." }
Create a migration project
After creating an Azure Database Migration Service instance, you will create a migration project. A migration project specifies the type of migration that needs to be done.
The following script creates a migration project named myfirstmysqlofflineproject for offline migration from MySQL to Azure Database for MySQL under the Database Migration Service instance created in Step 4 and in the same region.
# Get a reference to the DMS project - Create if not exists
[string] $ProjectName = "myfirstmysqlofflineproject"
$dmsProjectResourceId = "/subscriptions/$($global:currentSubscriptionId)/resourceGroups/$($dmsService.ResourceGroupName)/providers/Microsoft.DataMigration/services/$($dmsService.Name)/projects/$projectName"
$dmsProject = Get-AzResource -ResourceId $dmsProjectResourceId -ErrorAction SilentlyContinue
# Create Azure DMS Project if not existing
if (-not($dmsProject)) {
LogMessage -Message "Creating Azure DMS project $projectName for MySQL migration ..." -IsProcessing $true
$newProjectProperties = @{"sourcePlatform" = "MySQL"; "targetPlatform" = "AzureDbForMySQL" }
$dmsProject = New-AzResource `
-ApiVersion 2018-03-31-preview `
-Location $dmsService.Location `
-ResourceId $dmsProjectResourceId `
-Properties $newProjectProperties `
-Force
LogMessage -Message "Created Azure DMS project $projectName - $($dmsProject.ResourceId)."
}
else { LogMessage -Message "Azure DMS project $projectName exists." }
Create a Database Connection Info object for the source and target connections
After creating the migration project, you will create the database connection information. This connection information will be used to connect to the source and target servers during the migration process.
The following script takes the server name, user name and password for the source and target MySQL instances and creates the connection information objects. The script prompts the user to enter the password for the source and target MySQL instances. For silent scripts, the credentials can be fetched from Azure Key Vault.
# Initialize the source and target database server connections
[string] $SourceServerName = "13.66.136.192"
[string] $SourceUserName = "docadmin@mysqlserver"
[securestring] $SourcePassword = Read-Host "Enter MySQL Source Server Password" -AsSecureString
[string] $TargetServerName = "migdocdevwus2mysqlsstrgt.mysql.database.azure.com"
[string] $TargetUserName = "docadmin@migdocdevwus2mysqlsstrgt"
[securestring] $TargetPassword = Read-Host "Enter MySQL Target Server Password" -AsSecureString
function InitConnection(
[string] $ServerName,
[string] $UserName,
[securestring] $Password) {
$connectionInfo = @{
"dataSource" = "";
"serverName" = "";
"port" = 3306;
"userName" = "";
"password" = "";
"authentication" = "SqlAuthentication";
"encryptConnection" = $true;
"trustServerCertificate" = $true;
"additionalSettings" = "";
"type" = "MySqlConnectionInfo"
}
$connectionInfo.dataSource = $ServerName;
$connectionInfo.serverName = $ServerName;
$connectionInfo.userName = $UserName;
$connectionInfo.password = (ConvertFrom-SecureString -AsPlainText $password).ToString();
$connectionInfo;
}
# Initialize the source and target connections
LogMessage -Message "Initializing source and target connection objects ..." -IsProcessing $true
$sourceConnInfo = InitConnection `
$SourceServerName `
$SourceUserName `
$SourcePassword;
$targetConnInfo = InitConnection `
$TargetServerName `
$TargetUserName `
$TargetPassword;
LogMessage -Message "Source and target connection object initialization complete."
Extract the list of table names from the target database
Database table list can be extracted using a migration task and connection information. The table list will be extracted from both the source database and target database so that proper mapping and validation can be done.
The following script takes the names of the source and target databases and then extracts the table list from the databases using the GetUserTablesMySql migration task.
# Run scenario to get the tables from the target database to build
# the migration table mapping
[string] $TargetDatabaseName = "migtargetdb"
[string] $SourceDatabaseName = "migsourcedb"
function RunScenario([object] $MigrationService,
[object] $MigrationProject,
[string] $ScenarioTaskName,
[object] $TaskProperties,
[bool] $WaitForScenario = $true) {
# Check if the scenario task already exists, if so remove it
LogMessage -Message "Removing scenario if already exists..." -IsProcessing $true
Remove-AzDataMigrationTask `
-ResourceGroupName $MigrationService.ResourceGroupName `
-ServiceName $MigrationService.Name `
-ProjectName $MigrationProject.Name `
-TaskName $ScenarioTaskName `
-Force;
# Start the new scenario task using the provided properties
LogMessage -Message "Initializing scenario..." -IsProcessing $true
New-AzResource `
-ApiVersion 2018-03-31-preview `
-Location $MigrationService.Location `
-ResourceId "/subscriptions/$($global:currentSubscriptionId)/resourceGroups/$($MigrationService.ResourceGroupName)/providers/Microsoft.DataMigration/services/$($MigrationService.Name)/projects/$($MigrationProject.Name)/tasks/$($ScenarioTaskName)" `
-Properties $TaskProperties `
-Force | Out-Null;
LogMessage -Message "Waiting for $ScenarioTaskName scenario to complete..." -IsProcessing $true
if ($WaitForScenario) {
$progressCounter = 0;
do {
if ($null -ne $scenarioTask) {
Start-Sleep 10;
}
# Get calls can time out and will return a cancellation exception in that case
$scenarioTask = Get-AzDataMigrationTask `
-ResourceGroupName $MigrationService.ResourceGroupName `
-ServiceName $MigrationService.Name `
-ProjectName $MigrationProject.Name `
-TaskName $ScenarioTaskName `
-Expand `
-ErrorAction Ignore;
Write-Progress -Activity "Scenario Run $ScenarioTaskName (Marquee Progress Bar)" `
-Status $scenarioTask.ProjectTask.Properties.State `
-PercentComplete $progressCounter
$progressCounter += 10;
if ($progressCounter -gt 100) { $progressCounter = 10 }
}
while (($null -eq $scenarioTask) -or ($scenarioTask.ProjectTask.Properties.State -eq "Running") -or ($scenarioTask.ProjectTask.Properties.State -eq "Queued"))
}
Write-Progress -Activity "Scenario Run $ScenarioTaskName" `
-Status $scenarioTask.ProjectTask.Properties.State `
-Completed
# Now get it using REST APIs so we can expand the output
LogMessage -Message "Getting expanded task results ..." -IsProcessing $true
$psToken = (Get-AzAccessToken -ResourceUrl https://management.azure.com).Token;
$token = ConvertTo-SecureString -String $psToken -AsPlainText -Force;
$taskResource = Invoke-RestMethod `
-Method GET `
-Uri "https://management.azure.com$($scenarioTask.ProjectTask.Id)?api-version=2018-03-31-preview&`$expand=output" `
-ContentType "application/json" `
-Authentication Bearer `
-Token $token;
$taskResource.properties;
}
# create the get table task properties by initializing the connection and
# database name
$getTablesTaskProperties = @{
"input" = @{
"connectionInfo" = $null;
"selectedDatabases" = $null;
};
"taskType" = "GetUserTablesMySql";
};
LogMessage -Message "Running scenario to get the list of tables from the target database..." -IsProcessing $true
$getTablesTaskProperties.input.connectionInfo = $targetConnInfo;
$getTablesTaskProperties.input.selectedDatabases = @($TargetDatabaseName);
# Create a name for the task
$getTableTaskName = "$($TargetDatabaseName)GetUserTables"
# Get the list of tables from the source
$getTargetTablesTask = RunScenario -MigrationService $dmsService `
-MigrationProject $dmsProject `
-ScenarioTaskName $getTableTaskName `
-TaskProperties $getTablesTaskProperties;
if (-not ($getTargetTablesTask)) { throw "ERROR: Could not get target database $TargetDatabaseName table information." }
LogMessage -Message "List of tables from the target database acquired."
LogMessage -Message "Running scenario to get the list of tables from the source database..." -IsProcessing $true
$getTablesTaskProperties.input.connectionInfo = $sourceConnInfo;
$getTablesTaskProperties.input.selectedDatabases = @($SourceDatabaseName);
# Create a name for the task
$getTableTaskName = "$($SourceDatabaseName)GetUserTables"
# Get the list of tables from the source
$getSourceTablesTask = RunScenario -MigrationService $dmsService `
-MigrationProject $dmsProject `
-ScenarioTaskName $getTableTaskName `
-TaskProperties $getTablesTaskProperties;
if (-not ($getSourceTablesTask)) { throw "ERROR: Could not get source database $SourceDatabaseName table information." }
LogMessage -Message "List of tables from the source database acquired."
Build table mapping based on user configuration
As part of configuring the migration task, you will create a mapping between the source and target tables. The mapping is at the table name level but the assumption is that the table structure (column count, column names, data types etc.) of the mapped tables is exactly the same.
The following script creates a mapping based on the target and source table list extracted in Step 7. For partial data load, the user can provide a list of table to filter out the tables. If no user input is provided, then all target tables are mapped. The script also checks if a table with the same name exists in the source or not. If table name does not exists in the source, then the target table is ignored for migration.
# Create the source to target table map
# Optional table settings
# DEFAULT: $IncludeTables = $null => include all tables for migration
# DEFAULT: $ExcludeTables = $null => exclude no tables from migration
# Exclude list has higher priority than include list
# Array of qualified source table names which should be migrated
[string[]] $IncludeTables = @("migsourcedb.coupons", "migsourcedb.daily_cash_sheets");
[string[]] $ExcludeTables = $null;
LogMessage -Message "Creating the table map based on the user input and database table information ..." `
-IsProcessing $true
$targetTables = $getTargetTablesTask.Output.DatabasesToTables."$TargetDatabaseName";
$sourceTables = $getSourceTablesTask.Output.DatabasesToTables."$SourceDatabaseName";
$tableMap = New-Object 'system.collections.generic.dictionary[string,string]';
$schemaPrefixLength = $($SourceDatabaseName + ".").Length;
$tableMappingError = $false
foreach ($srcTable in $sourceTables) {
# Removing the database name prefix from the table name so that comparison
# can be done in cases where database name given are different
$tableName = $srcTable.Name.Substring($schemaPrefixLength, `
$srcTable.Name.Length - $schemaPrefixLength)
# In case the table is part of exclusion list then ignore the table
if ($null -ne $ExcludeTables -and $ExcludeTables -contains $srcTable.Name) {
continue;
}
# Either the include list is null or the table is part of the include list then add it in the mapping
if ($null -eq $IncludeTables -or $IncludeTables -contains $srcTable.Name) {
# Check if the table exists in the target. If not then log TABLE MAPPING ERROR
if (-not ($targetTables | Where-Object { $_.name -ieq "$($TargetDatabaseName).$tableName" })) {
$tableMappingError = $true
Write-Host "TABLE MAPPING ERROR: $($targetTables.name) does not exists in target." -ForegroundColor Red
continue;
}
$tableMap.Add("$($SourceDatabaseName).$tableName", "$($TargetDatabaseName).$tableName");
}
}
# In case of any table mapping errors identified, throw an error and stop the process
if ($tableMappingError) { throw "ERROR: One or more table mapping errors were identified. Please see previous messages." }
# In case no tables are in the mapping then throw error
if ($tableMap.Count -le 0) { throw "ERROR: Could not create table mapping." }
LogMessage -Message "Migration table mapping created for $($tableMap.Count) tables."
Create and configure the migration task inputs
After building the table mapping, you will create the inputs for migration task of type Migrate.MySql.AzureDbForMySql and configure the properties.
The following script creates the migration task and sets the connections, database names and table mapping.
# Create and configure the migration scenario based on the connections
# and the table mapping
$offlineMigTaskProperties = @{
"input" = @{
"sourceConnectionInfo" = $null;
"targetConnectionInfo" = $null;
"selectedDatabases" = $null;
"optionalAgentSettings" = @{
"EnableCacheBatchesInMemory" = $true;
"DisableIncrementalRowStatusUpdates" = $true;
};
"startedOn" = $null;
};
"taskType" = "Migrate.MySql.AzureDbForMySql";
};
$offlineSelectedDatabase = @{
"name" = $null;
"targetDatabaseName" = $null;
"tableMap" = $null;
};
LogMessage -Message "Preparing migration scenario configuration ..." -IsProcessing $true
# Select the database to be migrated
$offlineSelectedDatabase.name = $SourceDatabaseName;
$offlineSelectedDatabase.tableMap = New-Object PSObject -Property $tableMap;
$offlineSelectedDatabase.targetDatabaseName = $TargetDatabaseName;
# Set connection info and the database mapping
$offlineMigTaskProperties.input.sourceConnectionInfo = $sourceConnInfo;
$offlineMigTaskProperties.input.targetConnectionInfo = $targetConnInfo;
$offlineMigTaskProperties.input.selectedDatabases = @($offlineSelectedDatabase);
$offlineMigTaskProperties.input.startedOn = [System.DateTimeOffset]::UtcNow.ToString("O");
Configure performance tuning parameters
As pert of the PowerShell module, there are few optional parameters available, which can be tuned based on the environment. These parameters can be used to improve the performance of the migration task. All these parameters are optional and their default value is NULL.
Note
The following performance configurations have shown increased throughput during migration on Premium SKU.
- WriteDataRangeBatchTaskCount = 12
- DelayProgressUpdatesInStorageInterval = 30 seconds
- ThrottleQueryTableDataRangeTaskAtBatchCount = 36
The following script takes the user values of the parameters and sets the parameters in the migration task properties.
# Setting optional parameters from fine tuning the data transfer rate during migration
# DEFAULT values for all the configurations is $null
LogMessage -Message "Adding optional migration performance tuning configuration ..." -IsProcessing $true
# Partitioning settings
# Optional setting that configures the maximum number of parallel reads on tables located on the source database.
[object] $DesiredRangesCount = 4
# Optional setting that configures that size of the largest batch that will be committed to the target server.
[object] $MaxBatchSizeKb = 4096
# Optional setting that configures the minimum number of rows in each batch written to the target.
[object] $MinBatchRows = $null
# Task count settings
# Optional setting that configures the number of databases that will be prepared for migration in parallel.
[object] $PrepareDatabaseForBulkImportTaskCount = $null
# Optional setting that configures the number of tables that will be prepared for migration in parallel.
[object] $PrepareTableForBulkImportTaskCount = $null
# Optional setting that configures the number of threads available to read ranges on the source.
[object] $QueryTableDataRangeTaskCount = 8
# Optional setting that configures the number of threads available to write batches to the target.
[object] $WriteDataRangeBatchTaskCount = 12
# Batch cache settings
# Optional setting that configures how much memory will be used to cache batches in memory before reads on the source are throttled.
[object] $MaxBatchCacheSizeMb = $null
# Optional setting that configures the amount of available memory at which point reads on the source will be throttled.
[object] $ThrottleQueryTableDataRangeTaskAtAvailableMemoryMb = $null
# Optional setting that configures the number of batches cached in memory that will trigger read throttling on the source.
[object] $ThrottleQueryTableDataRangeTaskAtBatchCount = 36
# Performance settings
# Optional setting that configures the delay between updates of result objects in Azure Table Storage.
[object] $DelayProgressUpdatesInStorageInterval = "00:00:30"
function AddOptionalSetting($optionalAgentSettings, $settingName, $settingValue) {
# If no value specified for the setting, don't bother adding it to the input
if ($null -eq $settingValue) {
return;
}
# Add a new property to the JSON object to capture the setting which will be customized
$optionalAgentSettings | add-member -MemberType NoteProperty -Name $settingName -Value $settingValue
}
# Set any optional settings in the input based on parameters to this cmdlet
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "DesiredRangesCount" $DesiredRangesCount;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "MaxBatchSizeKb" $MaxBatchSizeKb;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "MinBatchRows" $MinBatchRows;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "PrepareDatabaseForBulkImportTaskCount" $PrepareDatabaseForBulkImportTaskCount;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "PrepareTableForBulkImportTaskCount" $PrepareTableForBulkImportTaskCount;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "QueryTableDataRangeTaskCount" $QueryTableDataRangeTaskCount;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "WriteDataRangeBatchTaskCount" $WriteDataRangeBatchTaskCount;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "MaxBatchCacheSizeMb" $MaxBatchCacheSizeMb;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "ThrottleQueryTableDataRangeTaskAtAvailableMemoryMb" $ThrottleQueryTableDataRangeTaskAtAvailableMemoryMb;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "ThrottleQueryTableDataRangeTaskAtBatchCount" $ThrottleQueryTableDataRangeTaskAtBatchCount;
AddOptionalSetting $offlineMigTaskProperties.input.optionalAgentSettings "DelayProgressUpdatesInStorageInterval" $DelayProgressUpdatesInStorageInterval;
Creating and running the migration task
After configuring the input for the task, now the task will be created and executed on the agent. The script triggers the task execution and wait for the migration to complete.
The following script invokes the configured migration task and waits for it to complete.
# Running the migration scenario
[string] $TaskName = "mysqlofflinemigrate"
LogMessage -Message "Running data migration scenario ..." -IsProcessing $true
$summary = @{
"SourceServer" = $SourceServerName;
"SourceDatabase" = $SourceDatabaseName;
"TargetServer" = $TargetServerName;
"TargetDatabase" = $TargetDatabaseName;
"TableCount" = $tableMap.Count;
"StartedOn" = $offlineMigTaskProperties.input.startedOn;
}
Write-Host "Job Summary:" -ForegroundColor Yellow
Write-Host $(ConvertTo-Json $summary) -ForegroundColor Yellow
$migrationResult = RunScenario -MigrationService $dmsService `
-MigrationProject $dmsProject `
-ScenarioTaskName $TaskName `
-TaskProperties $offlineMigTaskProperties
LogMessage -Message "Migration completed with status - $($migrationResult.state)"
#Checking for any errors or warnings captured by the task during migration
$dbLevelResult = $migrationResult.output | Where-Object { $_.resultType -eq "DatabaseLevelOutput" }
$migrationLevelResult = $migrationResult.output | Where-Object { $_.resultType -eq "MigrationLevelOutput" }
if ($dbLevelResult.exceptionsAndWarnings) {
Write-Host "Following database errors were captured: $($dbLevelResult.exceptionsAndWarnings)" -ForegroundColor Red
}
if ($migrationLevelResult.exceptionsAndWarnings) {
Write-Host "Following migration errors were captured: $($migrationLevelResult.exceptionsAndWarnings)" -ForegroundColor Red
}
if ($migrationResult.errors.details) {
Write-Host "Following task level migration errors were captured: $($migrationResult.errors.details)" -ForegroundColor Red
}
Deleting the Database Migration Service
The same Database Migration Service can be used for multiple migrations so the instance once created can be re-used. If you're not going to continue to use the Database Migration Service, then you can delete the service using the Remove-AzDataMigrationService command.
The following script deletes the Azure Database Migration Service instance and its associated projects.
Remove-AzDataMigrationService -ResourceId $($dmsService.ResourceId)
Next steps
- For information about known issues and limitations when performing migrations using DMS, see the article Common issues - Azure Database Migration Service.
- For troubleshooting source database connectivity issues while using DMS, see the article Issues connecting source databases.
- For information about Azure Database Migration Service, see the article What is Azure Database Migration Service?.
- For information about Azure Database for MySQL, see the article What is Azure Database for MySQL?.
- For tutorial about using DMS via portal, see the article Tutorial: Migrate MySQL to Azure Database for MySQL offline using DMS
Tilbakemeldinger
Kommer snart: Gjennom 2024 faser vi ut GitHub Issues som tilbakemeldingsmekanisme for innhold, og erstatter det med et nytt system for tilbakemeldinger. Hvis du vil ha mer informasjon, kan du se: https://aka.ms/ContentUserFeedback.
Send inn og vis tilbakemelding for