Edit

Share via

Migration guide: SQL Server to Azure SQL Database

  • Article

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.

Diagram of migration process flow.

Prerequisites

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

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

Discover

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.

Assess

Note

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.

The Azure SQL migration extension for Azure Data Studio provides a seamless wizard based experience to assess, get Azure recommendations and migrate your SQL Server databases on-premises to SQL Server on Azure Virtual Machines. Besides, highlighting any migration blockers or warnings, the extension also includes an option for Azure recommendations to collect your databases' performance data to recommend a right-sized Azure SQL Managed Instance to meet the performance needs of your workload (with the least price).

You can use the Azure SQL Migration extension for Azure Data Studio to assess databases to get:

To assess your environment using the Azure SQL Migration extension, follow these steps:

  1. Open the Azure SQL Migration extension for Azure Data Studio.
  2. Connect to your source SQL Server instance
  3. Click the Migrate to Azure SQL button, in the Azure SQL Migration wizard in Azure Data Studio
  4. Select databases for assessment, then click on next
  5. Select your Azure SQL target, in this case, Azure SQL Database (Preview)
  6. Click on View/Select to review the assessment report
  7. Look 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.

To get an Azure recommendation using the Azure SQL Migration extension, follow these steps:

  1. Open the Azure SQL Migration extension for Azure Data Studio.
  2. Connect to your source SQL Server instance
  3. Click the Migrate to Azure SQL button, in the Azure SQL Migration wizard in Azure Data Studio
  4. Select databases for assessment, then click on next
  5. Select your Azure SQL target, in this case, Azure SQL Database (Preview)
  6. Navigate to the Azure recommendations sections, click on Get Azure recommendation
  7. Select Collect performance data now. Select a folder on your local computer to store the performance logs, and then select Start.
  8. After 10 minutes, Azure Data Studio indicates that a recommendation is available for Azure SQL Database.
  9. Check the Azure SQL Database card, in the Azure SQL target panel to review your Azure SQL Database SKU recommendation

To learn more, see Tutorial: Migrate SQL Server to Azure SQL Database (preview) offline in Azure Data Studio.

If the assessment encounters multiple blockers to confirm that your database is not ready for an Azure SQL Database migration, then alternatively consider:

Scaled assessments and analysis

The Azure SQL Migration extension for Azure Data Studio and Azure Migrate 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:

Important

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.

Deploy to an optimally sized managed instance

You can use the Azure SQL migration extension for Azure Data Studio to get right-sized Azure SQL Managed Instance recommendation. The extension collects performance data from your source SQL Server instance to provide right-sized Azure recommendation that meets your workload's performance needs with minimal cost. To learn more, see Get right-sized Azure recommendation for your on-premises SQL Server database(s)

Based on the information in the discover and assess phase, create an appropriately sized target Azure SQL Database. You can do so by using the Quickstart: Create a single database - Azure SQL Database.

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.

Migrate using the Azure SQL migration extension for Azure Data Studio

To perform an offline migration using Azure Data Studio, follow the high-level steps below. For a detailed step-by-step tutorial, see Tutorial: Migrate SQL Server to Azure SQL Database (preview) offline in Azure Data Studio.

  1. Download and install Azure Data Studio and the Azure SQL migration extension.
  2. Launch the Migrate to Azure SQL Migration wizard in the extension in Azure Data Studio.
  3. Select databases for assessment and view migration readiness or issues (if any). Additionally, collect performance data and get right-sized Azure recommendation.
  4. Select your Azure account and your target Azure SQL Database from your subscription.
  5. Select the list of tables to migrate.
  6. Create a new Azure Database Migration Service using the wizard in Azure Data Studio. If you've previously created an Azure Database Migration Service using Azure Data Studio, you can reuse the same if desired.
  7. Optional: If your backups are on an on-premises network share, download and install self-hosted integration runtime on a machine that can connect to the source SQL Server, and the location containing the backup files.
  8. Start the database migration and monitor the progress in Azure Data Studio. You can also monitor the progress under the Azure Database Migration Service resource in Azure portal.

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.

Important

For details on the specific steps associated with performing a cutover as part of migrations using DMS, see Performing migration cutover.

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.

Tip

You can also use transactional replication to migrate a subset of your source database. The publication that you replicate to Azure SQL Database can be limited to a subset of the tables in the database being replicated. For each table being replicated, you can limit the data to a subset of the rows and/or a subset of the columns.

Transaction replication workflow

Important

Use the latest version of SQL Server Management Studio to remain synchronized with updates to Azure and SQL Database. Older versions of SQL Server Management Studio cannot set up SQL Database as a subscriber. Get the latest version of SQL Server Management Studio.

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.

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:

Important

Azure SQL Managed Instance enables you to migrate an existing SQL Server instance and its databases with minimal to no compatibility issues. See What is Azure SQL Managed Instance?

Next steps

See Service and tools for data 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.

To learn more about the Azure SQL Migration extension see:

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.