Database Migration: SQL Server 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 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 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

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

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:

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

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.

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.