Migration guide: SQL Server to Azure SQL Database

Applies to: SQL Server 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 EC2 (Elastic Compute Cloud)
• Amazon RDS (Relational Database Service) for SQL Server
• Google Compute Engine
• Cloud SQL for SQL Server - GCP (Google Cloud Platform)

For more migration information, see the migration overview. For other migration guides, see Database Migration.

Prerequisites

For your SQL Server migration to Azure SQL Database, make sure you have:

• Chosen your 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.

Pre-migration

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 migration.

Discover

In the discover phase, scan the network to identify all SQL Server instances and features used by your organization.

You can 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.

Assess

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:

• Azure target recommendations
• Azure SKU recommendations

To assess your environment using the Database Migration Assessment, follow these steps:

1. Open the Data Migration Assistant (DMA).
2. Select File and then choose New assessment.
3. Specify a project name, select SQL Server as the source server type, and then select Azure SQL Database as the target server type.
4. 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, and recommendations to address them (CONNECT SQL, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions required).
5. Specify the source connection details for your SQL Server and connect to the source database.
6. Select Start assessment.
7. 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.
8. Determine the database compatibility level that minimizes post-migration efforts.
9. 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.

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:

• Performing scaled assessments using PowerShell
• Analyzing assessment reports using Power BI

Migrate

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:

1. Download and install the Database Migration Assistant.
2. Create a new project and select Migration as the project type.
3. 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.
4. 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.
5. 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.
6. Select the schema objects and deploy them to the target Azure SQL Database.
7. 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.

Azure Database Migration Service (DMS)

To migrate databases from SQL Server to Azure SQL Database using DMS, follow the steps below:

1. If you haven't already, register the Microsoft.DataMigration resource provider in your subscription.
2. 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.
3. 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.
4. 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.
5. Map the source and target databases for migration and then select the tables you want to migrate.
6. 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 cut over 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 doesn't affect business continuity.

Migrate using transactional replication

When you can't afford to remove your SQL Server database from production while the migration is occurring, you can use SQL Server transactional replication as your migration solution. To use this method, the source database must meet the requirements for transactional replication and be compatible for Azure SQL Database. For information about SQL replication with availability groups, see Configure replication for Always On availability groups (SQL Server).

To use this solution, you configure your database in Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate. The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue.

With transactional replication, all changes to your data or schema show up in your database in Azure SQL Database. Once the synchronization is complete and you're ready to migrate, change the connection string of your applications to point them to your database. Once transactional replication drains any changes left on your source database and all your applications point to Azure SQL Database, you can uninstall transactional replication. Your database in Azure SQL Database is now your production system.

Transaction replication workflow

Step	Method
Set up distribution	SQL Server Management Studio | Transact-SQL
Create publication	SQL Server Management Studio | Transact-SQL
Create subscription	SQL Server Management Studio | Transact-SQL

Some tips and differences for migrating to SQL Database

• Use a local distributor
  • Doing so causes a performance impact on the server.
  • If the performance impact is unacceptable you can use another server, but it adds complexity in management and administration.
• When selecting a snapshot folder, make sure the folder you select is large enough to hold a BCP of every table you want to replicate.
• Snapshot creation locks the associated tables until it's complete, so schedule your snapshot appropriately.
• Only push subscriptions are supported in Azure SQL Database. You can only add subscribers from the source database.

Migration recommendations

To speed up migration to Azure SQL Database, you should consider the following recommendations:

	Resource contention	Recommendation
Source (typically on premises)	The primary bottleneck during migration from the source is data file I/O and latency, which needs to be monitored carefully.	Based on data file I/O and latency, and depending on whether it's a virtual machine or physical server, you may have to engage your storage admin and explore options to mitigate the bottleneck.
Target (Azure SQL Database)	The biggest limiting factor is the log generation rate and latency on your database log file. With Azure SQL Database, you can get a maximum log generation rate of 96 MB/s.	To speed up migration, scale up the target Azure SQL database to Business Critical Gen5 8 vCore to get the maximum log generation rate of 96 MB/s, which also provides low latency for log files. The Hyperscale service tier provides a log rate of 100 MB/s regardless of chosen service level.
Network	The network bandwidth needed is equal to the maximum 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 the target.
Azure Database Migration Service (DMS)	There can be 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.

You can also consider these recommendations for best performance during the migration process.

• Choose the highest service tier and compute size that your budget allows to maximize the transfer performance. You can scale down after the migration completes to save money.
• If you use BACPAC files, minimize the distance between your BACPAC file and the destination data center.
• Disable auto update and auto create statistics during migration.
• Partition tables and indexes.
• Drop indexed views, and recreate them once finished.
• Remove rarely queried historical data to another database and migrate this historical data to a separate database in Azure SQL Database. You can then query this historical data using elastic queries.

Post-migration

After you have successfully completed the migration stage, go through the following 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.

Update statistics

Update statistics with full scan after the migration is completed.

Remediate applications

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.

Perform tests

The test approach for database migration consists of the following activities:

1. 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.
2. 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.
3. Run validation tests: Run the validation tests against the source and the target, and then analyze the results.
4. Run performance tests: Run performance test against the source and the target, and then analyze and compare the results.

Use advanced features

Be sure to take advantage of the advanced cloud-based features offered by SQL Database, such as built-in high availability, threat detection, and monitoring and tuning your workload.

Some SQL Server features are only available once the database compatibility level is changed to the latest compatibility level.

To learn more, see managing Azure SQL Database after migration.

Resolve database migration compatibility issues

You may encounter a wide variety of compatibility issues, depending both on the version of SQL Server in the source database and the complexity of the database you're migrating. Older versions of SQL Server have more compatibility issues. Use the following resources, in addition to a targeted Internet search using your search engine of choices:

• Transact-SQL differences between SQL Server and Azure SQL Database
• Discontinued Database Engine Functionality in SQL Server

Next steps

For a matrix of the Azure 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 SQL Database see:

• An Overview of Azure SQL Database
• Azure total Cost of Ownership Calculator

To learn more about the framework and adoption cycle for Cloud migrations, see:

• Cloud Adoption Framework for Azure
• Best practices for costing and sizing workloads for migration to Azure

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.