Today, we're announcing the public preview of Azure SQL Database offline migrations in the Azure SQL Migration extension for Azure Data Studio.
With this new feature, you can 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.
Azure SQL Migrations extension - Architecture overview
Prerequisites
The following list has all prerequisites you need to complete before migrating to Azure SQL Database using the Azure Migration extension for Azure Data Studio:
- Download and install Azure Data Studio
- Install the Azure SQL migration extension from the Azure Data Studio marketplace.
- Have an Azure account that is assigned one of the following built-in roles:
- Contributor for the target Azure SQL Database
- Reader role for the Azure Resource Groups having the target Azure SQL Database.
- Owner or Contributor role for the Azure subscription (needed if creating a new DMS (Azure Database Migration Service).
As an alternative to using the above built-in roles, you can assign a custom role as defined in this article.
- Create a target Azure SQL Database.
- Ensure that the SQL Server login to connect the source SQL Server is a member of db_datareader and the login for the target SQL server is db_owner.
- Migrate the database schema from source to target using the SQL Server dacpac extension or SQL Database Projects extension for Azure Data Studio.
- Ensure that the Microsoft.DataMigration resource provider is registered in your subscription. This is needed when using Azure Database Migration Service (DMS) for the first time.
It's essential to note that an Azure account is not required to complete the database assessment or Azure recommendation steps in the migration wizard. An Azure account is only required to configure the migration steps.
Migration assessment and right-sized recommendations
The new migration capability in the Azure SQL Migration extension provides an end-to-end experience to modernize SQL Server to Azure SQL Database. This allows you to perform a migration readiness check with actions to remediate migration blockers, export the assessment results, and get right-sized Azure recommendations.
Assessment - Azure SQL Database target
We're also introducing an all-new elastic recommendation model for generating right-sized Azure recommendations. The model uses a novel price-performance methodology developed in collaboration with Microsoft Research. By analyzing existing, already-migrated Azure workloads, this new recommendation model has been shown to correctly identify cost-saving opportunities compared to alternate recommendation approaches.
Azure SQL Database offline migrations
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.
With an offline migration, application downtime starts when the migration starts. This new migration experience in the Azure SQL Migration extension considers an acceptable downtime during the migration process.
As mentioned in the prerequisites section, deploy the database schema from source to target using the SQL Server dacpac extension or SQL Database Projects extension for Azure Data Studio before starting your migration.
The migration to Azure SQL Database is considered a logical migration. Thus, backups are not required for this type of migration. The migration process performed by the Azure SQL extension understands just the data movement (performed by DMS) from source to target.
You can check the latest known migration status returned by DMS using the SQL Migration dashboard (within the Azure SQL Migration extension) or the Azure Portal.
SQL Migration dashboard
The following table has more details about the possible statuses:
Status |
Description |
Preparing for copy |
Disabling autostats, triggers, and indexes for the target table |
Copying |
Data is being copied from source to target |
Copy finished |
Data copy has finished, and, waiting on other tables to finish copying to begin the final steps to return tables to the original schema |
Rebuilding indexes |
Rebuilding indexes on target tables |
Succeeded |
This table has all data copied to it and indexes rebuilt |
Perform migrations at scale using PowerShell and Azure CLI
Using Azure PowerShell Az.DataMigration or Azure CLI az datamigration, you can migrate databases by automating the creation of the Azure Database Migration Service and configuring offline database migrations for Azure SQL Database targets.
Check out our Azure Samples GitHub repository to learn more about scaling your migrations through PowerShell and Azure CLI. You can use these examples to automate your migration process, whether an online or offline migration to Azure SQL Managed instance or SQL Server on Virtual Machines using file share or Azure storage.
Migration scenario |
Scripting language |
SQL Server assessment |
|
SQL Server to Azure SQL Managed Instance (using file share) |
|
SQL Server to Azure SQL Managed Instance (using Azure storage) |
|
SQL Server to SQL Server on Azure Virtual Machines (using file share) |
|
SQL Server to SQL Server on Azure Virtual Machines (using Azure Storage) |
|
SQL Server to Azure SQL Database (Preview) |
|
SKU recommendations (Preview) |
|
End-to-End migration automation |
|
End-to-End migration automation for multiple databases |
Conclusion
The Azure SQL migration extension for Azure Data Studio provides you with the ability 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.
To learn more about Azure SQL Database Offline migrations, see the tutorial Migrate SQL Server to an Azure SQL Database offline using Azure Data Studio with DMS. Take advantage of this One-click SQL Migration PoC GitHub repository to accelerate your migration to Azure SQL.
We look forward to hearing about your experience using the Azure Database Migration extension for Azure Data Studio. Please don't hesitate to contact us with questions or suggestions at the Azure Community forum — Azure Database Migration Service forum.
Updated Nov 09, 2022
Version 1.0carlosrobles
Microsoft
Joined October 17, 2022
Microsoft Data Migration Blog
Follow this blog board to get notified when there's new activity