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)
- Azure CLI (Already installed)
- Az datamigration extension
- Azure storage account (Already provisioned)
- Azure Data Studio (Already installed)
- Azure SQL Migration extension for Azure Data Studio
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.
-
Install az datamigration extension if it isn't installed.
az extension add --name datamigration -
Run the following to log in from your client using your default web browser
az loginIf you have more than one subscription, you can select a particular subscription.
az account set --subscription <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 Azure CLI command az datamigration can be used to manage data migration at scale.
-
Backup database
Backups must be taken before starting the migration:
- Create SAS tokens for your storage containers
- Create a SQL Server credential using a shared access signature
- Database backup to URL
The following T-SQL is an example that creates the credential to use a Shared Access Signature and creates a backup.
USE master CREATE CREDENTIAL [https://storagemigration.blob.core.windows.net/migration] -- this name must match the container path, start with https and must not contain a forward slash at the end WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and should not be changed , SECRET = 'XXXXXXX' -- this is the shared access signature key. Don't forget to remove the first character "?" GO -- Back up the full AdventureWorks2019 database to the container BACKUP DATABASE AdventureWorks2019 TO URL = 'https://storagemigration.blob.core.windows.net/migration/AdventureWorks2019.bak' WITH CHECKSUM
Caution
- Connect to the Jump Box VM
- VM name: jb-migration
- Use the credentials provided on the deploy page.
Use the az datamigration sql-managed-instance create command to create and start a database migration.
```azurecli
az datamigration sql-managed-instance create `
--source-location '{\"AzureBlob\":{\"storageAccountResourceId\":\"/subscriptions/<subscription id>/resourceGroups/<resource group name>/providers/Microsoft.Storage/storageAccounts/<storage account name>\",\"accountKey\":\"<storage key>\",\"blobContainerName\":\"migration\"}}' `
--migration-service "/subscriptions/<subscription id>/resourceGroups/<resource group name>/providers/Microsoft.DataMigration/SqlMigrationServices/MySqlMigrationService" `
--scope "/subscriptions/<subscription id>/resourceGroups/<resource group name>/providers/Microsoft.Sql/managedInstances/<azure sql mi instance name>" `
--source-database-name "AdventureWorks2019" `
--source-sql-connection authentication="SqlAuthentication" data-source="10.1.0.4" password="My$upp3r$ecret" user-name="sqladmin" `
--target-db-name "AdventureWorks" `
--resource-group <resource group name> `
--managed-instance-name <azure sql mi instance name>
--offline-configuration last-backup-name="<backup name>.bak" offline=true
```
The following example creates and starts a migration of complete source database with target database name AdventureWorks:
```azurecli
az datamigration sql-managed-instance create `
--source-location '{\"AzureBlob\":{\"storageAccountResourceId\":\"/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/oneclickpoc/providers/Microsoft.Storage/storageAccounts/storagepocmigration\",\"accountKey\":\"XXXXXX\",\"blobContainerName\":\"migration\"}}' `
--migration-service "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/oneclickpoc/providers/Microsoft.DataMigration/SqlMigrationServices/MySqlMigrationService" `
--scope "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/oneclickpoc/providers/Microsoft.Sql/managedInstances/sqlmicsapocmigration" `
--source-database-name "AdventureWorks2019" `
--source-sql-connection authentication="SqlAuthentication" data-source="10.1.0.4" password="My$upp3r$ecret" user-name="sqladmin" `
--target-db-name "AdventureWorks" `
--resource-group oneclickpoc `
--managed-instance-name sqlmicsapocmigration
--offline-configuration last-backup-name="AdventureWorks2019.bak" offline=true
```
Tip
You should take all necessary backups.
Learn more about using CLI to migrate
Use the az datamigration sql-db show command to monitor migration.
-
Basic migration details
az datamigration sql-managed-instance show --managed-instance-name "<azure sql mi instance name>" --resource-group "<resource group name>" --target-db-name "AdventureWorks" -
Gets complete migration detail
az datamigration sql-managed-instance show --managed-instance-name "<azure sql mi instance name>" --resource-group "<resource group name>" --target-db-name "AdventureWorks" --expand=MigrationStatusDetails -
ProvisioningState should be "Creating", "Failed" or "Succeeded"
az datamigration sql-managed-instance show --managed-instance-name "<azure sql mi instance name>" --resource-group "<resource group name>" --target-db-name "AdventureWorks" --expand=MigrationStatusDetails --query "properties.provisioningState" -
MigrationStatus should be "InProgress", "Canceling", "Failed" or "Succeeded"
az datamigration sql-managed-instance show --managed-instance-name "<azure sql mi instance name>" --resource-group "<resource group name>" --target-db-name "AdventureWorks" --expand=MigrationStatusDetails --query "properties.migrationStatus"
This script performs an end to end migration of multiple databases in multiple servers