Azure SQL Database offline migrations are now in General Availability for Azure Database Migration Service (Azure DMS). With this new feature, you can assess, get right-sized Azure recommendations for Azure SQL Server migration targets, and migrate databases offline from on-premises SQL Servers, SQL Servers on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database. With the new Azure SQL Migration extension (powered by Azure DMS) for Azure Data Studio, you can now enjoy a simplified assessment, recommendation, and migration experience, accompanied by a range of powerful capabilities.
For large-scale migrations involving multiple databases across multiple SQL Server instances, you can leverage automation tools such as PowerShell - Azure DataMigration Service Module or Azure CLI. These tools, combined with the capabilities of the Azure SQL migration extension (powered by Azure DMS), enable you to migrate your databases efficiently and effortlessly.
In this blog post, I'll guide you through the process of performing an offline migration of the AdventureWorks2016 database. The migration involves moving the database from an on-premises SQL Server to an Azure SQL Database server. To achieve this, we'll use Microsoft Azure PowerShell and specifically the New-Az.DataMigration module, which provides the tools and functionalities necessary to facilitate the migration of databases from a SQL Server instance to an Azure SQL Database server.
Prerequisites
Before migrating to Azure SQL Database using the Azure Migration extension for Azure Data Studio, be sure that the following prerequisites are in place.
- An Azure subscription. If you don't have one, create a free account before you begin.
- A resource group.
- An Azure account that is assigned to one of the built-in roles listed below:
- Contributor for the target Azure SQL Database.
- Reader role for the Azure Resource Groups having the target Azure SQL Database instance.
- Owner or Contributor role for the Azure subscription (needed if creating a new DMS (Azure Database Migration Service).
Note: As an alternative to using the above built-in roles, you can assign a custom role as defined in this article.
- A SQL Server instance with the AdventureWorks2016 database as a source database.
- A target instance of Azure SQL Database.
- Ensure that the SQL Server login to connect the source SQL Server is a member of the db_datareader and the login for the target SQL server is db_owner.
- Integration Runtime installed or its downloaded .MSI file, which you can download from here.
- A completed Azure SQL assessment or Assessment via Azure SQL migration extension in Azure Data Studio on the source SQL server to verify that the migration to Azure SQL Database is possible.
- A database schema migrated from the source to the target using the SQL Server dacpac extension or the SQL Database Projects extension for Azure Data Studio.
- If you're using the Azure DMSfor the first time, ensure that Microsoft.DataMigration resource provider is registered in your subscription. You can follow the steps to register the resource provider
- An installation of the test version of Az.DataMigration, which you can install from here.
End-to-end migration to Azure SQL Database with Microsoft Azure PowerShell
The end-to-end migration to Azure SQL Database with Microsoft Azure PowerShell involves four key stages:
- Building the migration workflow using the Az.DataMigration module in Microsoft Azure PowerShell
- Validating the migration
- Testing the application
- Finalizing the migration
Build the migration workflow to Azure SQL Database by using the Az.DataMigration module in Microsoft Azure PowerShell
To build a migration workflow to Azure SQL Database using the Az.DataMigration module in Microsoft Azure PowerShell, you need to follow these steps:
- Create an instance of Azure DMS.
- Register the instance of Azure DMS with the self-hosted Integration Runtime.
- Deploy the schema using SqlPackage (DACPAC).
Note: You can skip this step if schema at the target database already exists. - Start database migration (offline) to Azure SQL Database.
- Monitor database migration.
Execute the following PowerShell script to create a migration workflow using Azure Data Migration Service (DMS). This script automates the migration process and ensures a smooth transition to Azure SQL Database. The following example creates and starts a migration of source database name AdventureWorks2016 with target database name TargetDB. Please run the commands in PowerShell as an Administrator as Register-AzDataMigrationIntegrationRuntime command requires admin permissions.
# Step 0- This step requires you to declare the migration variables in order to perform the migration successfully.
# Step 1- Create an instance of Database Migration Service
# Step 2- Register Database Migration Service with self-hosted Integration Runtime
# Step 3- Schema deployment using SqlPackage (DACPAC). Skip this step if schema at the target database already exists.
# Step 4- Start Database Migration (Offline) to Azure SQL Database
# Step 5- Monitoring Database Migration
# ------------------------------------------------------------------------------------
# Step 0- Declare Migration variables before executing the Powershell script
# DataSource is the name or IP of a SQL Server instance or Azure SQL Database.
$SQLPasswordLocal = "XXXXXXX"
$SQLPasswordDB = "XXXXXXX”
# Error control
$ErrorActionPreference = "Stop";
$migrationInput = @{
dmsInfo = @{
subscriptionId = "xxxxxxxxxxx-ae01-a0f0601a2ca9";
resourceGroupName = "DMSdemo";
serviceName = "AzureDMS";
location = "east us";
};
sqlSource = @{
dataSource = "2x.2xx.xx.xx3";
userName = "dbadmin";
password = $(ConvertTo-SecureString -AsPlainText -Force $SQLPasswordLocal);
authenticationtype = "SQLAuthentication";
databaseName = " AdventureWorks2016"
};
sqlTarget = @{
instanceName = "targetservername5wlgxxxxzxc6";
resourceGroupName = "DMSdemo";
dataSource = "targetservernamexxxxxxkzzzxc6.database.windows.net";
userName = "dbadmin";
password = $(ConvertTo-SecureString -AsPlainText -Force $SQLPasswordDB);
authenticationtype = "SQLAuthentication";
databaseName = "TargetDB"
};
};
# Step 0- Migration (EOF) variables
$shirMsiPath = "D:\IntegrationRuntime_5.31.8556.1.msi";
$sqlPackageDownloadPath = "D:\sqlpackage-win7-x64-en-US-16.0.5400.1.zip";
$script:sqlPackagePath = $null;
# Step 1- Create an instance of Database Migration Service
# Main section of the script
function Invoke-Main {
# Connect to your Azure account and set subscription
Connect-AzAccount -Subscription $migrationInput.dmsInfo.subscriptionId;
# Create an instance of Database Migration Service
New-AzDataMigrationSqlService -ResourceGroupName $migrationInput.dmsInfo.resourceGroupName -Name $migrationInput.dmsInfo.serviceName -Location $migrationInput.dmsInfo.location;
# Step 2- Register Database Migration Service with self-hosted Integration Runtime
# Register Database Migration Service with self-hosted Integration Runtime
$authKeys = Get-AzDataMigrationSqlServiceAuthKey -ResourceGroupName $migrationInput.dmsInfo.resourceGroupName -SqlMigrationServiceName $migrationInput.dmsInfo.serviceName;
Register-AzDataMigrationIntegrationRuntime -AuthKey $authKeys.AuthKey1 -IntegrationRuntimePath $shirMsiPath;
# Step 3- Schema deployment using SqlPackage (DACPAC). Skip step 3 if schema at the target database already exists.
# (optional)- Invoke a function to perform schema deployment using SqlPackage (DACPAC).
function Invoke-SqlSchemaDeployment {
# Download SqlPackage from Microsoft
Get-SqlPackageBinaries -outputBasePath $PSScriptRoot;
# Extract schema as from source as dacpac
$dacpacPath = ("{0}\{1}.dacpac" -f $PSScriptRoot, $migrationInput.sqlSource.databaseName);
$extractArgs = @(
"/Action:Extract",
"/TargetFile:""$dacpacPath""",
"/p:ExtractAllTableData=false",
"/p:ExtractReferencedServerScopedElements=false",
"/p:VerifyExtraction=true",
"/p:IgnoreUserLoginMappings=true", ###
"/SourceServerName:$($migrationInput.sqlSource.dataSource)",
"/SourceDatabaseName:$($migrationInput.sqlSource.databaseName)",
"/SourceUser:$($migrationInput.sqlSource.userName)",
"/SourcePassword:$(ConvertTo-UnSecureString -password $migrationInput.sqlSource.password)");
# run the cmd
& "$script:sqlPackagePath" @extractArgs | Out-Null;
# check exit code
if ($LastExitCode -eq 0) {
Write-Host ("Info: Extract of $dacpacPath completed") -ForegroundColor Green;
} else {
Write-Error ("Error: Failed to extract schema from source. ExitCode={0}." -f $LastExitCode);
}
# Publish schema to target using dacpac from previous step
$publishArgs = @(
"/Action:Publish",
"/SourceFile:""$dacpacPath""",
"/p:CreateNewDatabase=false",
"/p:AllowIncompatiblePlatform=true",
"/p:ExcludeObjectTypes=Users;RoleMembership",
"/Diagnostics:false",
"/TargetServerName:$($migrationInput.sqlTarget.dataSource)",
"/TargetDatabaseName:$($migrationInput.sqlTarget.databaseName)",
"/TargetUser:$($migrationInput.sqlTarget.userName)",
"/TargetPassword:$(ConvertTo-UnSecureString -password $migrationInput.sqlTarget.password)",
"/TargetTrustServerCertificate:true");
# run the cmd
& "$script:sqlPackagePath" @publishArgs | Out-Null;
if ($LastExitCode -eq 0) {
Write-Host ("Info: Publish of $dacpacPath completed") -ForegroundColor Green;
} else {
Write-Error ("Error: Failed to publish schema to target. ExitCode={0}." -f $LastExitCode);
}
}
function Get-SqlPackageBinaries {
param(
[Parameter(Mandatory=$true)]
[string]$outputBasePath
)
# reference : https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver16
$sqlPackageUri = $sqlPackageDownloadPath;
$outSqlPackagePath = "$outputBasePath\SqlPackagex64";
$sqlPackageZipPath = "$outSqlPackagePath.zip";
$sqlPackageExePath = "$outSqlPackagePath\SqlPackage.exe";
if (-not(Test-Path -Path $sqlPackageExePath)) {
Write-Host ("Downloading SqlPackage binaries to '{0}'" -f $outSqlPackagePath);
# Start-BitsTransfer -Source $sqlPackageUri -Destination $sqlPackageZipPath;
$ProgressPreference = 'SilentlyContinue';
Invoke-WebRequest -Uri $sqlPackageUri -OutFile $sqlPackageZipPath;
Expand-Archive -Path $sqlPackageZipPath -DestinationPath $outSqlPackagePath -Force;
}
# Ensure SqlPackage.exe exists
if (-not (Test-Path -Path $sqlPackageExePath)) {
throw ("Error: Could not find '{0}'" -f $sqlPackageExePath);
}
$script:sqlPackagePath = $sqlPackageExePath;
}
function ConvertTo-UnSecureString {
param( [SecureString]$password )
if ($password.GetType().Name -eq "SecureString") {
return [Runtime.InteropServices.Marshal]::PtrToStringAuto($([Runtime.InteropServices.Marshal]::SecureStringToBSTR($password)));
} else { return $password; }
}
# Call this function to perform schema deployment using SqlPackage (DACPAC). Skip the entire step 3 if schema at the target database already exists.
# Invoke-SqlSchemaDeployment
# Step 4- Start Database Migration (Offline) to Azure SQL Database
# Call DMS to perform the Database migration to Azure SQL Database (offline)
$dmsResId = $("/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.DataMigration/SqlMigrationServices/{2}" -f $migrationInput.dmsInfo.subscriptionId, $migrationInput.dmsInfo.resourceGroupName, $migrationInput.dmsInfo.serviceName);
$sqldbResId = $("/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}" -f $migrationInput.dmsInfo.subscriptionId, $migrationInput.sqlTarget.resourceGroupName, $migrationInput.sqlTarget.instanceName);
New-AzDataMigrationToSqlDb `
-ResourceGroupName $migrationInput.sqlTarget.resourceGroupName `
-SqlDbInstanceName $migrationInput.sqlTarget.instanceName `
-TargetDbName $migrationInput.sqlTarget.databaseName `
-MigrationService $dmsResId `
-Scope $sqldbResId `
-SourceSqlConnectionAuthentication "SqlAuthentication" `
-SourceSqlConnectionDataSource $migrationInput.sqlSource.dataSource `
-SourceSqlConnectionUserName $migrationInput.sqlSource.userName `
-SourceSqlConnectionPassword $migrationInput.sqlSource.password `
-SourceDatabaseName $migrationInput.sqlSource.databaseName `
-TargetSqlConnectionAuthentication "SqlAuthentication" `
-TargetSqlConnectionDataSource $migrationInput.sqlTarget.dataSource `
-TargetSqlConnectionPassword $migrationInput.sqlTarget.password `
-TargetSqlConnectionUserName $migrationInput.sqlTarget.userName `
-WarningAction SilentlyContinue;
# Step 5- Monitoring Database Migration
# Monitoring Migration
Get-AzDataMigrationToSqlDb -ResourceGroupName $migrationInput.sqlTarget.resourceGroupName -SqlDbInstanceName $migrationInput.sqlTarget.instanceName -TargetDbName $migrationInput.sqlTarget.databaseName
}
# Calling main function to start end to end database migration workflow
Invoke-Main
After running the steps in the above PowerShell script, the subsequent results will be visible, as depicted in the provided screenshots.
1. Create an instance of Azure DMS
Use the New-AzDataMigrationToSqlDb cmdlet to create and start a database migration. The following screenshot displays creation of a DMS service named AzureDMS in the resource group DMSDemo located in the East US region.
2. Register the instance of Azure DMS with the self-hosted Integration Runtime
The following screenshot displays the creation of self-hosted integration times and gets the authkeys of the service we previously created. In the following steps, we pass the previously obtained authkeys and the path of Integration Runtime .MSI to install Integration Runtime and register the service on it.
3. Deploy the schema using SqlPackage (DACPAC)
For the purposes of this post, I skipped the schema creation process since it has already been accomplished during the migration of the database schema from the source to the target using either the SQL Server dacpac extension or the SQL Database Projects extension. Optionally, you can run step 3 using a script to create the schema at the destination, if doesn't exist.
4. Start database migration (offline) to Azure SQL Database
Before commencing the migration, ensure that you have sufficient downtime to bring the applications down at the source. Use the New-AzDataMigrationToSqlDb cmdlet to create and start a database migration. The following screenshot displays the creates and starts a migration of complete source database name AdventureWorks2016 with target database name TargetDB.
5. Monitor database migration
In the previous step, we have registered the instance of Azure DMS with the self-hosted Integration Runtime. You can also verify the status of Azure Integration runtime from Azure portal. To monitor the migration, perform the following steps:
i. In the Azure portal, search for your instance of Azure DMS by using the resource name.
ii. In the Azure DMS instance overview, select View Integration Runtime to view the details of your Integration runtime; the Integration runtime status appears as Online, as shown in the following screenshot:
iii. In the Database Migration Service instance overview, select Monitor migrations to view the details of migration. You will see Migration status of Source database AdventureWorks2016 in Progress.
iv. To monitor the migration status details, select migration success.
v. The migration is by default offline, so no cutover is required. When all table data is migrated to the Azure SQL Database TargetDB, Database Migration Service updates the migration status from In progress to Succeeded. Refresh the migration status page until you see the status is Succeeded.
vi. The migration is completed when migration status changes to succeeded.
vii. Now select the Source SQL Server from migrations tab, it displays the migration status of all the tables copies from Source database name AdventureWorks2016 to Azure SQL Database Target.
Validate the migration
Verify the transferred database objects and data on the target SQL Server instance to ensure that the migration was successful and there are no issues or discrepancies.
Test the application
Test the application that relies on the migrated database to ensure its functionality and performance in the new environment. This step confirms that the application is working correctly with the migrated Azure SQL database.
Finalize the migration
Once the migration is validated and the application is functioning properly, update the necessary configurations, connection strings, and dependencies to point to the migrated database on the target Azure SQL Database.
Conclusion
The Azure SQL migration extension for Azure Data Studio enables you to assess, get right-sized Azure recommendations for Azure migration targets, and migrate databases offline from on-premises SQL Server, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database. Take advantage of the flexibility and automation capabilities of PowerShell to simplify your Azure SQL Database migration. By following these steps and leveraging the power of Azure SQL Migration extension and Azure Data Studio, you can ensure a successful and efficient migration process.
- To learn more about Azure SQL Database offline migrations, see Tutorial: Migrate SQL Server to an Azure SQL Database offline using Azure Data Studio with DMS.
- You can find the documentation of each of the cmdlets here : DataMigration Cmdlets Doc.
- Learn more about Automating self-hosted integration runtime installation using local PowerShell scripts
If you have any feedback on this article or suggestions for future topics, please let me on by reaching out to me at gchekuri@microsoft.com. Thank you!
