Migration guide: SQL Server to Azure SQL Database
APPLIES TO: Azure SQL Database
In this guide, you learn how to migrate your SQL Server instance to Azure SQL Database.
You can migrate SQL Server running on-premises or on:
- SQL Server on Virtual Machines
- Amazon Web Services (AWS) EC2
- Amazon Relational Database Service (AWS RDS)
- Compute Engine (Google Cloud Platform - GCP)
- Cloud SQL for SQL Server (Google Cloud Platform – GCP)
For your SQL Server migration to Azure SQL Database, make sure you have:
- Chosen migration method and corresponding tools .
- Installed Data Migration Assistant (DMA) on a machine that can connect to your source SQL Server.
- Created a target Azure SQL Database.
- Configured connectivity and proper permissions to access both source and target.
- Reviewed the database engine features available in Azure SQL Database.
After you've verified that your source environment is supported, start with the pre-migration stage. Discover all of the existing data sources, assess migration feasibility, and identify any blocking issues that might prevent your Azure cloud migration.
In the Discover phase, scan the network to identify all SQL Server instances and features used by your organization.
Use Azure Migrate to assess migration suitability of on-premises servers, perform performance-based sizing, and provide cost estimations for running them in Azure.
Alternatively, use the Microsoft Assessment and Planning Toolkit (the "MAP Toolkit") to assess your current IT infrastructure. The toolkit provides a powerful inventory, assessment, and reporting tool to simplify the migration planning process.
For more information about tools available to use for the Discover phase, see Services and tools available for data migration scenarios.
If you are assessing the entire SQL Server data estate at scale on VMWare, use Azure Migrate to get Azure SQL deployment recommendations, target sizing, and monthly estimates.
After data sources have been discovered, assess any on-premises SQL Server database(s) that can be migrated to Azure SQL Database to identify migration blockers or compatibility issues.
You can use the Data Migration Assistant (version 4.1 and later) to assess databases to get:
To assess your environment using the Database Migration Assessment, follow these steps:
- Open the Data Migration Assistant (DMA).
- Select File and then choose New assessment.
- Specify a project name, select SQL Server as the source server type, and then select Azure SQL Database as the target server type.
- Select the type(s) of assessment reports that you want to generate. For example, database compatibility and feature parity. Based on the type of assessment, the permissions required on the source SQL Server can be different. DMA will highlight the permissions required for the chosen advisor before running the assessment.
- The feature parity category provides a comprehensive set of recommendations, alternatives available in Azure, and mitigating steps to help you plan your migration project. (sysadmin permissions required)
- The compatibility issues category identifies partially supported or unsupported feature compatibility issues that might block migration as well as recommendations to address them (
VIEW SERVER STATE, and
VIEW ANY DEFINITIONpermissions required).
- Specify the source connection details for your SQL Server and connect to the source database.
- Select Start assessment.
- After the process completes, select and review the assessment reports for migration blocking and feature parity issues. The assessment report can also be exported to a file that can be shared with other teams or personnel in your organization.
- Determine the database compatibility level that minimizes post-migration efforts.
- Identify the best Azure SQL Database SKU for your on-premises workload.
To learn more, see Perform a SQL Server migration assessment with Data Migration Assistant.
If the assessment encounters multiple blockers to confirm that your database it not ready for an Azure SQL Database migration, then alternatively consider:
- Azure SQL Managed Instance if there are multiple instance-scoped dependencies
- SQL Server on Azure Virtual Machines if both SQL Database and SQL Managed Instance fail to be suitable targets.
Scaled Assessments and Analysis
Data Migration Assistant supports performing scaled assessments and consolidation of the assessment reports for analysis.
If you have multiple servers and databases that need to be assessed and analyzed at scale to provide a wider view of the data estate, see the following links to learn more:
Running assessments at scale for multiple databases, especially large ones, can also be automated using the DMA Command Line Utility and uploaded to Azure Migrate for further analysis and target readiness.
After you have completed tasks associated with the Pre-migration stage, you are ready to perform the schema and data migration.
Migrate your data using your chosen migration method.
This guide describes the two most popular options - Data Migration Assistant and Azure Database Migration Service.
Data Migration Assistant (DMA)
To migrate a database from SQL Server to Azure SQL Database using DMA, follow these steps:
- Download and install the Database Migration Assistant.
- Create a new project and select Migration as the project type.
- Set the source server type to SQL Server and the target server type to Azure SQL Database, select the migration scope as Schema and data and select Create.
- In the migration project, specify the source server details such as the server name, credentials to connect to the server and the source database to migrate.
- In the target server details, specify the Azure SQL Database server name, credentials to connect to the server and the target database to migrate to.
- Select the schema objects and deploy them to the target Azure SQL Database.
- Finally, select Start data migration and monitor the progress of migration.
For a detailed tutorial, see Migrate on-premises SQL Server or SQL Server on Azure VMs to Azure SQL Database using the Data Migration Assistant.
- Scale your database to a higher service tier and compute size during the import process to maximize import speed by providing more resources. You can then scale down after the import is successful.
- The compatibility level of the imported database is based on the compatibility level of your source database.
Azure Database Migration Service (DMS)
To migrate databases from SQL Server to Azure SQL Database using DMS, follow the steps below:
- If you haven't already, register the Microsoft.DataMigration resource provider in your subscription.
- Create an Azure Database Migration Service Instance in a desired location of your choice (preferably in the same region as your target Azure SQL Database). Select an existing virtual network or create a new one to host your DMS instance.
- After your DMS instance is created, create a new migration project and specify the source server type as SQL Server and the target server type as Azure SQL Database. Choose Offline data migration as the activity type in the migration project creation blade.
- Specify the source SQL Server details on the Migration source details page and the target Azure SQL Database details on the Migration target details page.
- Map the source and target databases for migration and then select the tables you want to migrate.
- Review the migration summary and select Run migration. You can then monitor the migration activity and check the progress of your database migration.
For a detailed tutorial, see Migrate SQL Server to an Azure SQL Database using DMS.
Data sync and cutover
When using migration options that continuously replicate / sync data changes from source to the target, the source data and schema can change and drift from the target. During data sync, ensure that all changes on the source are captured and applied to the target during the migration process.
After you verify that data is same on both the source and the target, you can cutover from the source to the target environment. It is important to plan the cutover process with business / application teams to ensure minimal interruption during cutover does not affect business continuity.
For details on the specific steps associated with performing a cutover as part of migrations using DMS, see Performing migration cutover.
To speed up migration to Azure SQL Database, you should consider the following recommendations:
|Source (typically on premises)||Primary bottleneck during migration in source is DATA I/O and latency on DATA file which needs to be monitored carefully.||Based on DATA IO and DATA file latency and depending on whether it’s a virtual machine or physical server, you will have to engage storage admin and explore options to mitigate the bottleneck.|
|Target (Azure SQL Database)||Biggest limiting factor is the log generation rate and latency on log file. With Azure SQL Database, you can get a maximum of 96-MB/s log generation rate.||To speed up migration, scale up the target SQL DB to Business Critical Gen5 8 vCore to get the maximum log generation rate of 96 MB/s and also achieve low latency for log file. The Hyperscale service tier provides 100-MB/s log rate regardless of chosen service level|
|Network||Network bandwidth needed is equal to max log ingestion rate 96 MB/s (768 Mb/s)||Depending on network connectivity from your on-premises data center to Azure, check your network bandwidth (typically Azure ExpressRoute) to accommodate for the maximum log ingestion rate.|
|Virtual machine used for Data Migration Assistant (DMA)||CPU is the primary bottleneck for the virtual machine running DMA||Things to consider to speed up data migration by using - Azure compute intensive VMs - Use at least F8s_v2 (8 vcore) VM for running DMA - Ensure the VM is running in the same Azure region as target|
|Azure Database Migration Service (DMS)||Compute resource contention and database objects consideration for DMS||Use Premium 4 vCore. DMS automatically takes care of database objects like foreign keys, triggers, constraints, and non-clustered indexes and doesn't need manual intervention.|
After you have successfully completed the migration stage, go through a series of post-migration tasks to ensure that everything is functioning smoothly and efficiently.
The post-migration phase is crucial for reconciling any data accuracy issues and verifying completeness, as well as addressing performance issues with the workload.
After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this will, in some cases, require changes to the applications.
The test approach for database migration consists of the following activities:
- Develop validation tests: To test database migration, you need to use SQL queries. You must create the validation queries to run against both the source and the target databases. Your validation queries should cover the scope you have defined.
- Set up test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
- Run validation tests: Run the validation tests against the source and the target, and then analyze the results.
- Run performance tests: Run performance test against the source and the target, and then analyze and compare the results.
Leverage advanced features
Some SQL Server features are only available once the database compatibility level is changed to the latest compatibility level (150).
To learn more, see managing Azure SQL Database after migration
For a matrix of the Microsoft and third-party services and tools that are available to assist you with various database and data migration scenarios as well as specialty tasks, see Service and tools for data migration.
To learn more about Azure Migrate see
To learn more about SQL Database see:
To learn more about the framework and adoption cycle for Cloud migrations, see
To assess the Application access layer, see Data Access Migration Toolkit (Preview)
For details on how to perform Data Access Layer A/B testing see Database Experimentation Assistant.