Migration to Azure - Azure SQL Database

25 Feb 2021 4149 views 0 minutes to read Contributors

Migration to Azure - Azure SQL Database

Migrating database to the cloud, SQL Server instance to Azure SQL Database should verify that your source environment is supported and that you have addressed any prerequisites. This will help to ensure an efficient and successful migration.

Offline versus online migrations

When you migrate SQL Server databases to Azure by using Azure Database Migration Service, you can perform an offline or an online migration. With an offline migration, application downtime begins when the migration starts. For an online migration, downtime is limited to the time required to cut over to the new environment when the migration completes. It's recommended to test an offline migration to determine whether the downtime is acceptable; if not, perform an online migration.

Supported versions

This section describes all supported scenarios and options for an upgrade from on-premise SQL Server versions to Azure SQL Database. This information is current as of August 2019.

Details for migrations to Azure SQL Database from the following SQL Server sources are included:

  • SQL Server 2005
  • SQL Server 2008 and SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server 2017

You can migrate SQL Server running on-premises or on:

  • SQL Server on Virtual Machines
  • Amazon Web Services (AWS) EC2
  • Compute Engine (GCP)
  • AWS RDS

The following data migration options are discussed:

Data migration options, details, and supported versions are provided in the following table.

 

Migration option

Purpose

Azure Database Migration Service

Azure DMS supports online (minimal downtime) and offline (one time) migrations at scale to an Azure SQL Database managed instance from SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017.

Data Migration Assistant

Use DMA to detect compatibility issues that can impact database functionality in your target Azure SQL Database managed instance, to recommend performance and reliability improvements, and to move the schema, data, and uncontained objects from your source server to your target server. For information about supported sources and targets, and to download the latest version of DMA, see the Microsoft Download Center.

Transactional replication

Transactional replication to an Azure SQL Database managed instance is supported for migrations from:

  • SQL Server 2012 (SP2 CU8, SP3, or later)
  • SQL Server 2014 (RTM CU10 or later, or SP1 CU3 or later)
  • SQL Server 2016
  • SQL Server 2017

Bulk load

Use bulk load to an Azure SQL Database managed instance for data stored in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017.

 

Pre-migration overview

After verifying that your source environment is supported and ensuring that you have addressed any prerequisites, you are ready to start the Pre-migration stage. This part of the process involves conducting an inventory of the databases that you need to migrate, assessing those databases for potential migration issues or blockers, and then resolving any items you might have uncovered. For heterogenous migrations (such as Oracle to Azure Database for PostgreSQL), this stage also involves converting the schema(s) in the source database(s) to be compatible with the target environment. For homogenous migrations, such as SQL Server to Azure SQL Database, conversion of the source schema to work in the target environment is not required.

Discover

The goal of the Discover phase is to identify existing data sources and details about the features that are being used to get a better understanding of and plan for the migration. This process involves scanning the network to identify all your organization’s SQL Server instances together with the version and features in use.

You can use the Azure Migrate service to assesses on-premises workloads for migration to Azure. The service assesses the migration suitability of on-premises computers, performs performance-based sizing, and provides cost estimations for running on-premises computers in Azure. If you're contemplating lift-and-shift migrations, or are in the early assessment stages of migration, this service is for you.

You can also use the Microsoft Assessment and Planning Toolkit (the "MAP Toolkit") to assess your current IT infrastructure for a variety of technology migration projects. This Solution Accelerator provides a powerful inventory, assessment, and reporting tool to simplify the migration planning process.

For more information about the tools available for use during the Discover phase, see the article Services and tools available for data migration scenarios.

Assess

When the data sources have been identified, the next step is to assess on-premises SQL Server instance(s) migrating to Azure SQL database(s) to understand the gaps between the source and target instances. Use the Data Migration Assistant (DMA) to assess your source database before migrating to Azure SQL Database.

You can use Data Migration Assistant to assess databases to get Azure SQL Database SKU recommendations. For more information, see the article here.

Overview of assessment steps

An overview of the steps associated with using DMA to create an assessment follows.

  1. Open the Data Migration Assistant (DMA), and then begin creating a new assessment project.
  2. Specify a project name, select SQL Serveras the source server type, and then select Azure SQL Database as the target server type.
  3. Select the type(s) of assessment reports (database compatibility and feature parity) that you want to generate.
    • The SQL Server feature parity category provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps to help you plan the effort into your migration projects.
    • The Compatibility issues category identifies partially supported or unsupported features that reflect compatibility issues that might block migrating on-premises SQL Server database(s) to an Azure SQL Database managed instance. Recommendations are also provided to help you address those issues.
  1. Specify the source connection details for your SQL Server, connect to the source database, and then start the assessment.
  2. When the process is complete, review the assessment reports for migration blocking issues and feature parity issues by selecting the specific options.
  3. Determine the database compatibility level that you want to minimize your efforts after migrating to Azure SQL Database.
  4. Identify the best Azure SQL Database managed instance SKU for your on-premises workload.

 

 Migration overview

After you have the necessary prerequisites in place and have completed the tasks associated with the Pre-migration stage, you are ready to perform the schema and data migration.

Migrate schema

After you are comfortable with the assessment and satisfied that the selected database is a viable candidate for migration to Azure SQL Database, use the Data Migration Assistant (DMA) to migrate the schema to Azure SQL Database.

Note: Before you create a migration project in DMA, be sure that you have already provisioned an Azure SQL database as mentioned in the prerequisites.

Overview of schema migration steps

An overview of the steps associated with using DMA to migrate the schema follows.

  1. Open DMA, and then begin creating a new migration project.
  2. Specify a project name, select SQL Server as the source server type, and then select Azure SQL Database as the target server type.
  3. Specify the migration scope as Schema only, and then create the project.
  4. Specify the source connection details for your SQL Server, and then connect to the source database.
  5. Specify the target connection details for the Azure SQL database, and then connect to the database you had pre-provisioned in Azure SQL Database.
  6. Specify the schema objects in the source database that need to be deployed to Azure SQL Database.
  7. Generate SQL scripts, and then review them for any errors.
  8. Fix the objects that report errors by leveraging the recommendations provided by your DMA assessment.
  9. Deploy the schema to Azure SQL Database, and then check the target server for any anomalies.

Important: For detail on the specific steps associated with:

  • Online migrations, see the information here.
  • Offline migrations, see the information here.

Migrate data

When your schema has been successfully migrated to the target, the next step is to execute the data movement by using the Azure Database Migration Service (DMS).

Note: You can also use DMA to migrate databases. However, DMA is best for performing proofs of concept (PoCs), test database migrations, or for relatively small database migration efforts. Note that when using DMA for data migration, you can only move one database at a time. For more information, see the article Migrate on-premises SQL Server using Data Migration Assistant.

Overview of data migration steps

An overview of the steps associated with using Azure DMS to migrate the data follows.

  1. Register the Microsoft.DataMigration resource provider.
  2. Create an instance of DMS.
  3. Create a migration project in DMS.
  4. Specify source details for the migration.
  5. Specify target details for the migration.
  6. Run the migration.
  7. Monitor the migration.

Important: For detail on the specific steps associated with:

  • Online migrations, see the information here.
  • Offline migrations, see the information here.

Data sync and Cutover

With minimal-downtime migrations, the source you are migrating continues to change, drifting from the target in terms of data and schema, after the one-time migration occurs. During the Data sync phase, you need to ensure that all changes in the source are captured and applied to the target in near real time. After you verify that all changes in source have been applied to the target, you can cutover from the source to the target environment.

 

Post-migration overview

After you have successfully completed the Migration stage, you need to go through a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.

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

Note: For assistance with developing and running post-migration validation tests, consider the Data Quality Solution available from the partner QuerySurge.

Optimize

The post migration phase is crucial for reconciling any issues with data accuracy and completeness, as well as addressing potential performance issues with the workload.

 

In this article