Migrate SQL Server databases at scale to Azure using PowerShell / CLI

Published Mar 30 2022 09:26 AM 1,729 Views

The Azure SQL Migration extension for Azure Data Studio provides a seamless and robust migration experience to help you migrate your SQL Server databases to Azure. However, if you have multiple databases across multiple SQL Server instances, migrating them together at scale using graphical UI may not be the most efficient method. Today, we are announcing Public Preview of new migration capabilities added to Az.DataMigration module in Azure PowerShell and az datamigration extension in Azure CLI. With these migration capabilities added to Azure PowerShell and Azure CLI, you can automate tasks and activities related to SQL Server database assessments, performance data collection for Azure recommendations and migration of multiple databases (across multiple SQL Server instances) at scale.

 

By automating migration activities, you can save time and efficiently migrate a large number of SQL Server databases to Azure SQL Managed Instance or SQL Server on Azure Virtual Machines. For example, the code snippet below demonstrates a SQL Server database migration to Azure SQL Managed Instance using Azure CLI with three commands.

 

Step 1: Create a new Azure Database Migration service in your Azure subscription.

 

 

az datamigration sql-service create --resource-group "myRG" --sql-migration-service-name "myMigrationService" --location "EastUS2"

 

 

Step 2: Configure and start online database migration from SQL Server on-premises (with backups in Azure Storage) to Azure SQL Managed Instance.

 

 

az datamigration sql-managed-instance create `
--source-location '{\"AzureBlob\":{\"storageAccountResourceId\":\"/subscriptions/mySubscriptionID/resourceGroups/myRG/providers/Microsoft.Storage/storageAccounts/dbbackupssqlbits\",\"accountKey\":\"myAccountKey\",\"blobContainerName\":\"dbbackups\"}}' `
--migration-service "/subscriptions/mySubscriptionID/resourceGroups/myRG/providers/Microsoft.DataMigration/SqlMigrationServices/myMigrationService" `
--scope "/subscriptions/mySubscriptionID/resourceGroups/myRG/providers/Microsoft.Sql/managedInstances/mySQLMI" `
--source-database-name "AdventureWorks2008" `
--source-sql-connection authentication="SqlAuthentication" data-source="mySQLServer" password="myPassword" user-name="sqluser" `
--target-db-name "AdventureWorks2008" `
--resource-group myRG `
--managed-instance-name mySQLMI

 

 

Step 3: Perform a migration cutover once all backups are restored to Azure SQL Managed Instance.

 

 

$migOpId = az datamigration sql-managed-instance show --managed-instance-name "mySQLMI" --resource-group "myRG" --target-db-name "AdventureWorks2008" --expand=MigrationStatusDetails --query "properties.migrationOperationId"
az datamigration sql-managed-instance cutover --managed-instance-name "mySQLMI" --resource-group "myRG" --target-db-name "AdventureWorks2008" --migration-operation-id $migOpId

 

 

Sample scripts to run end-to-end SQL Server database migrations to Azure SQL Managed Instance or SQL Server on Azure Virtual Machines are available in the Azure Samples Github repository (aka.ms/sqldatamigration-samples).

 

To learn more, see documentation [Migrate databases at scale using automation]

 

%3CLINGO-SUB%20id%3D%22lingo-sub-3271086%22%20slang%3D%22en-US%22%3EMigrate%20SQL%20Server%20databases%20at%20scale%20to%20Azure%20using%20PowerShell%20%2F%20CLI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3271086%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdms%2Fmigration-using-azure-data-studio%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20SQL%20Migration%20extension%20for%20Azure%20Data%20Studio%3C%2FA%3E%20provides%20a%20seamless%20and%20robust%20migration%20experience%20to%20help%20you%20migrate%20your%20SQL%20Server%20databases%20to%20Azure.%20However%2C%20if%20you%20have%20multiple%20databases%20across%20multiple%20SQL%20Server%20instances%2C%20migrating%20them%20together%20at%20scale%20using%20graphical%20UI%20may%20not%20be%20the%20most%20efficient%20method.%20Today%2C%20we%20are%20announcing%20Public%20Preview%20of%20new%20migration%20capabilities%20added%20to%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fpowershell%2Fmodule%2Faz.datamigration%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAz.DataMigration%20module%20in%20Azure%20PowerShell%3C%2FA%3E%26nbsp%3Band%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fcli%2Fazure%2Fdatamigration%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eaz%20datamigration%20extension%20in%20Azure%20CLI%3C%2FA%3E.%20With%20these%20migration%20capabilities%20added%20to%20Azure%20PowerShell%20and%20Azure%20CLI%2C%20you%20can%20automate%20tasks%20and%20activities%20related%20to%20SQL%20Server%20database%20assessments%2C%20performance%20data%20collection%20for%20Azure%20recommendations%20and%20migration%20of%20multiple%20databases%20(across%20multiple%20SQL%20Server%20instances)%20at%20scale.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20automating%20migration%20activities%2C%20you%20can%20save%20time%20and%20efficiently%20migrate%20a%20large%20number%20of%20SQL%20Server%20databases%20to%20Azure%20SQL%20Managed%20Instance%20or%20SQL%20Server%20on%20Azure%20Virtual%20Machines.%20For%20example%2C%20the%20code%20snippet%20below%20demonstrates%20a%20SQL%20Server%20database%20migration%20to%20Azure%20SQL%20Managed%20Instance%20using%20Azure%20CLI%20with%20three%20commands.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%201%3A%20Create%20a%20new%20Azure%20Database%20Migration%20service%20in%20your%20Azure%20subscription.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3Eaz%20datamigration%20sql-service%20create%20--resource-group%20%22myRG%22%20--sql-migration-service-name%20%22myMigrationService%22%20--location%20%22EastUS2%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%202%3A%20Configure%20and%20start%20online%20database%20migration%20from%20SQL%20Server%20on-premises%20(with%20backups%20in%20Azure%20Storage)%20to%20Azure%20SQL%20Managed%20Instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3Eaz%20datamigration%20sql-managed-instance%20create%20%60%0A--source-location%20'%7B%5C%22AzureBlob%5C%22%3A%7B%5C%22storageAccountResourceId%5C%22%3A%5C%22%2Fsubscriptions%2FmySubscriptionID%2FresourceGroups%2FmyRG%2Fproviders%2FMicrosoft.Storage%2FstorageAccounts%2Fdbbackupssqlbits%5C%22%2C%5C%22accountKey%5C%22%3A%5C%22myAccountKey%5C%22%2C%5C%22blobContainerName%5C%22%3A%5C%22dbbackups%5C%22%7D%7D'%20%60%0A--migration-service%20%22%2Fsubscriptions%2FmySubscriptionID%2FresourceGroups%2FmyRG%2Fproviders%2FMicrosoft.DataMigration%2FSqlMigrationServices%2FmyMigrationService%22%20%60%0A--scope%20%22%2Fsubscriptions%2FmySubscriptionID%2FresourceGroups%2FmyRG%2Fproviders%2FMicrosoft.Sql%2FmanagedInstances%2FmySQLMI%22%20%60%0A--source-database-name%20%22AdventureWorks2008%22%20%60%0A--source-sql-connection%20authentication%3D%22SqlAuthentication%22%20data-source%3D%22mySQLServer%22%20password%3D%22myPassword%22%20user-name%3D%22sqluser%22%20%60%0A--target-db-name%20%22AdventureWorks2008%22%20%60%0A--resource-group%20myRG%20%60%0A--managed-instance-name%20mySQLMI%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%203%3A%20Perform%20a%20migration%20cutover%20once%20all%20backups%20are%20restored%20to%20Azure%20SQL%20Managed%20Instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24migOpId%20%3D%20az%20datamigration%20sql-managed-instance%20show%20--managed-instance-name%20%22mySQLMI%22%20--resource-group%20%22myRG%22%20--target-db-name%20%22AdventureWorks2008%22%20--expand%3DMigrationStatusDetails%20--query%20%22properties.migrationOperationId%22%0Aaz%20datamigration%20sql-managed-instance%20cutover%20--managed-instance-name%20%22mySQLMI%22%20--resource-group%20%22myRG%22%20--target-db-name%20%22AdventureWorks2008%22%20--migration-operation-id%20%24migOpId%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESample%20scripts%20to%20run%20end-to-end%20SQL%20Server%20database%20migrations%20to%20Azure%20SQL%20Managed%20Instance%20or%20SQL%20Server%20on%20Azure%20Virtual%20Machines%20are%20available%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure-Samples%2Fdata-migration-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Samples%20Github%3C%2FA%3E%20repository%20(%3CA%20href%3D%22https%3A%2F%2Faka.ms%2Fsqldatamigration-samples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eaka.ms%2Fsqldatamigration-samples%3C%2FA%3E).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20learn%20more%2C%20see%20documentation%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdms%2Fmigration-dms-powershell-cli%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%5BMigrate%20databases%20at%20scale%20using%20automation%5D%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3271086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Managed%20Instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Server%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Server%20on%20Azure%20VMs%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 30 2022 09:26 AM
Updated by: