Perform offline migrations of your SQL Server databases running on-premises, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database using the Azure SQL Migration extension.
- SQL Server with Windows authentication or SQL authentication access
- .Net Core 3.1 (Already installed)
- Az.DataMigration PowerShell module
Caution
- Connect to the Jump Box VM
- VM name: jb-migration
- Use the credentials provided on the deploy page.
Open a Terminal. It is already installed in the VM and by default it uses PowerShell.
The Azure SQL migration extension for Azure Data Studio enables you to assess, get Azure recommendations and migrate your SQL Server databases to Azure.
In addition, the Azure PowerShell command Az.DataMigration can be used to manage data migration at scale.
-
Run the following to log in from your client using your default web browser if you are not logged in.
Connect-AzAccount -Subscription <Subscription-id>
If you have more than one subscription, you can select a particular subscription.
Set-AzContext -SubscriptionId <subscription-id>
The Azure SQL migration extension for Azure Data Studio enables you to assess, get Azure recommendations and migrate your SQL Server databases to Azure.
In addition, the PowerShell command Data Migration can be used to manage data migration at scale.
-
Backup database
USE master BACKUP DATABASE AdventureWorks2019 TO Disk = 'C:\temp\backup\AdventureWorks2019.bak' WITH CHECKSUM
-
Use the Get-AzDataMigrationSqlServiceAuthKey command to obtain AuthKeys.
$AuthKeys = Get-AzDataMigrationSqlServiceAuthKey ` -ResourceGroupName "<resource group name>" ` -SqlMigrationServiceName "PoCMigrationService"
- The following example obtains the authKey:
$AuthKeys = Get-AzDataMigrationSqlServiceAuthKey ` -ResourceGroupName "oneclickpoc" ` -SqlMigrationServiceName "PoCMigrationService"
-
Change the PowerShell execution policy.
Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned
-
Use the az datamigration register-integration-runtime command to register the service on Integration Runtime.
Register-AzDataMigrationIntegrationRuntime ` -AuthKey <authKey> `
The following example registers the service on Integration Runtime:
Register-AzDataMigrationIntegrationRuntime ` -AuthKey $AuthKeys.AuthKey1 `
[!WARNING] If you receive an error message saying: "RegisterIntegrationRuntime.ps1 cannot be loaded because running scripts is disabled on this system", please, run the following command and re-run the PowerShell command above.
Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned
Caution
- Connect to the Jump Box VM
- VM name: jb-migration
- Use the credentials provided on the deploy page.
-
Convert the passwords to secure string
$sourcePassword = ConvertTo-SecureString "My`$upp3r`$ecret" -AsPlainText -Force $sourceFileSharePassword = ConvertTo-SecureString "My`$upp3r`$ecret" -AsPlainText -Force
-
Use the New-AzDataMigrationToSqlManagedInstance command to create and start a database migration.
New-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName <resource group name> ` -ManagedInstanceName <azure sql mi instance name> ` -TargetDbName "AdventureWorks" ` -Kind "SqlMI" ` -Scope "/subscriptions/<subscription id>/resourceGroups/<resource group name>/providers/Microsoft.Sql/managedInstances/<azure sql mi instance name>" ` -MigrationService "/subscriptions/<subscription id>/resourceGroups/<resource group name>/providers/Microsoft.DataMigration/SqlMigrationServices/PoCMigrationService" ` -StorageAccountResourceId "/subscriptions/<subscription id>/resourceGroups/<resource group name>/providers/Microsoft.Storage/storageAccounts/<storage account name>" ` -StorageAccountKey "<storage key>" ` -FileSharePath "\\sqlvm-001\SQLBackup" ` -FileShareUsername "sqlvm-001\sqladmin" ` -FileSharePassword $sourceFileSharePassword ` -SourceSqlConnectionAuthentication "SqlAuthentication" ` -SourceSqlConnectionDataSource "10.1.0.4" ` -SourceSqlConnectionUserName "sqladmin" ` -SourceSqlConnectionPassword $sourcePassword ` -SourceDatabaseName "AdventureWorks2019" ` -Offline
The following example creates and starts a migration of complete source database with target database name AdventureWorks:
New-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName oneclickpoc ` -ManagedInstanceName sqlmicsapocmigration ` -TargetDbName "AdventureWorks" ` -Kind "SqlMI" ` -Scope "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/oneclickpoc/providers/Microsoft.Sql/managedInstances/sqlmicsapocmigration" ` -MigrationService "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/oneclickpoc/providers/Microsoft.DataMigration/SqlMigrationServices/PoCMigrationService" ` -StorageAccountResourceId "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/oneclickpoc/providers/Microsoft.Storage/storageAccounts/storagepocmigration" ` -StorageAccountKey "XXXXXXXX" ` -FileSharePath "\\10.1.0.4\SQLBackup" ` -FileShareUsername "10.1.0.4\sqladmin" ` -FileSharePassword $sourceFileSharePassword ` -SourceSqlConnectionAuthentication "SqlAuthentication" ` -SourceSqlConnectionDataSource "10.1.0.4" ` -SourceSqlConnectionUserName "sqladmin" ` -SourceSqlConnectionPassword $sourcePassword ` -SourceDatabaseName "AdventureWorks2019" ` -Offline
Tip
You should take all necessary backups.
Learn more about using Powershell to migrate
Use the Get-AzDataMigrationToSqlManagedInstance command to monitor migration.
-
Get complete migration details
$monitoringMigration = Get-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName <resource group name> ` -SqlDbInstanceName <azure sql db instance> ` -TargetDbName AdventureWorks ` -Expand MigrationStatusDetails $monitoringMigration
The following example brings complete details
$monitoringMigration = Get-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName oneclickpoc ` -SqlDbInstanceName sqlservercsapocmigration ` -TargetDbName AdventureWorks ` -Expand MigrationStatusDetails $monitoringMigration
-
ProvisioningState should be Creating, Failed, or Succeeded
$monitoringMigration = Get-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName <resource group name> ` -SqlDbInstanceName <azure sql db instance> ` -TargetDbName AdventureWorks ` -Expand MigrationStatusDetails $monitoringMigration.ProvisioningState | Format-List
The following example brings complete details
$monitoringMigration = Get-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName oneclickpoc ` -SqlDbInstanceName sqlservercsapocmigration ` -TargetDbName AdventureWorks ` -Expand MigrationStatusDetails $monitoringMigration.ProvisioningState | Format-List
-
MigrationStatus should be InProgress, Canceling, Failed, or Succeeded
$monitoringMigration = Get-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName <resource group name> ` -SqlDbInstanceName <azure sql db instance> ` -TargetDbName AdventureWorks ` -Expand MigrationStatusDetails $monitoringMigration.MigrationStatus | Format-List
The following example brings complete details
$monitoringMigration = Get-AzDataMigrationToSqlManagedInstance ` -ResourceGroupName oneclickpoc ` -SqlDbInstanceName sqlservercsapocmigration ` -TargetDbName AdventureWorks ` -Expand MigrationStatusDetails $monitoringMigration.MigrationStatus | Format-List
You can also use the Azure Portal to monitor migration.
This script performs an end to end migration of multiple databases in multiple servers