Migrating SQL Server to Azure SQL Database provides essential benefits. Such advantages include the following:
- lower cost by shifting expenses from a capital expenditure model to an operational expenditure model
- improved security with encryption on by default plus access threat protection
- improved scalability and high availability
- ease of maintenance, including automatic backup and recovery and cloud-based disaster recovery
A successful migration should include preparation, pre-migration, migration, and post-migration. This article focuses on migrating SQL Server databases using the recommended Microsoft tools for small to medium and large databases. We will start with preparation.
Preparation
Preparation starts with:
- Review your offline and online migration options.
- Understand what tools are available.
- Verify that Azure SQL Database supports your source database instances.
- Check all the prerequisites.
Offline Versus Online Migration
You can perform an offline or online migration using Azure Database Migration Service (DMS). For offline migration, application downtime begins at once to make sure no change occurs from start to finish. For an online migration, application downtime is only limited to after the migration starts and the cutover phase is complete.
Review the migration tool options in the next section and use a test environment on a limited dataset to decide which makes more sense.
Migration Tools
The following two sections outline the migration tools, their purpose, and the supported SQL Server versions.
Azure Database Migration Service (DMS)
Azure Database Migration Service (DMS) is a managed cloud service that supports online and offline migrations to Azure SQL Database and Azure SQL Managed Instances. It uses Microsoft Data Migration Assistant (DMA) for assessment and recommendations. It supports migrating SQL Server from version 2005 to version 2019 (Windows and Linux, starting with version 2017).
Microsoft Data Migration Assistant (DMA)
Microsoft Data Migration Assistant (DMA) is a Windows client-based tool for performing assessments and migrations. Azure DMS uses it to conduct assessments but can also perform migrations for small to medium size database migrations. It supports migrating SQL Server from version 2005 to version 2017 (Windows only).
Prerequisites
You should address the following prerequisite tool options before proceeding with pre-migration:
- For Microsoft DMA, download and install the latest version from Microsoft’s website.
- For Azure DMS, an offering in Microsoft’s Azure Migrate hub, consult the prerequisites in Microsoft’s documentation.
Pre-Migration
Pre-migration is used to find any issues that could block migration and help resolve them before migration. Microsoft calls this the assessment phase. You can conduct this using Microsoft DMA.
An assessment begins with creating a project and selecting a source database. Assessments check database compatibility and feature parity issues based on each compatibility level. The assessments contain migration blockers, behavior changes, deprecated features, and information issues.
You can open, load, save, export, restart, and delete assessments. For future reference, you can mark the assessment project status as New, In-Progress, Error, or Completed. Assessments can also help to determine the Azure stock-keeping unit (SKU) recommendations using files that contain extended event sessions or traces. This is optional when using Microsoft DMA. Recommendations from the assessment of this additional information can include a pricing tier, compute level, max data size, and estimated monthly cost. You can develop a TCO versus return on investment (ROI) and business case using Microsoft’s total cost of ownership (TCO) calculator. Once Microsoft DMA completes an assessment, you can upload it to Azure Migrate.
For more information on SKU recommendations, refer to “Identify the right Azure SQL Database/Managed Instance SKU for your on-premises database” in Microsoft’s documentation.
Migration
Migration performs the actual schema and data migration. Before you start the migration, ensure you have provisioned an Azure SQL Database resource as determined in the pre-migration step Azure SKU recommendations. You can use both Microsoft DMA and Azure DMS to perform migrations. However, Microsoft recommends that Microsoft DMA is best reserved for proofs of concept, test migrations, or small databases.
Schema Migration
The migrating schema comprises the following steps:
- Select source and target instances and the database, along with connection information.
- Select schema objects.
- Generate and review the SQL scripts created.
- Fix any errors in the SQL scripts.
- Deploy the schema and check the target.
Migrate Data
Microsoft recommends you should migrate data in two phases. Once you use a test copy of the database and a second time on a production copy, once you find that post-migration testing has no issues.
Migrating data comprises the following steps:
- Specify source and target details, including databases to migrate (schemas should already exist).
- Run and monitor the migration (sometimes called the data synchronization phase).
- Check the migration completion status.
Cutover
Once a data production migration is complete, ensure any applications that use the data work fine before completing the cutover.
For more information on cutover, refer to “Perform migration cutover” in Microsoft’s documentation.
Post-Migration
Post-migration includes testing, remediation, and optimization to help ensure that any application using the migrated data performs as expected. You should perform post-migration on a stage test environment just in case any remediation needs to occur. Once you decide that post-migration is successful, Microsoft recommends redoing migration using a production copy of the database to avoid downtime.
Testing
A good migration test plan should include the following elements:
- Develop validation tests that gather sample queries and expected performance characteristics for the source environment.
- Set up a test environment isolated from production with access to source and migrated data.
- Run validation tests using the sample queries against the source and target data and confirm the results.
- Run performance tests to analyze results against the migrated data and compare them to expected performance characteristics from the source environment.
Remediation
Remediate any application that consumes the source data or any problems in migrated data that may have surfaced from testing.
Optimization
As a last step, address any data quality or performance issues testing identifies.
For more information on addressing issues and specific steps to mitigate them, refer to “Post-migration validation and optimization guide” and “Tuning performance in Azure SQL Database” in Microsoft’s documentation.
Idera provides robust solutions for SQL Server, Azure SQL Database, and Amazon RDS for SQL Server:
- Quickly find and fix database performance problems with SQL Diagnostic Manager
- Track database assets and monitor database health with SQL Inventory Manager
- Tune database performance, backup, and security via expert recommendations and executable scripts with SQL Doctor
- Manage and monitor database security and compliance with SQL Secure
- Backup, restore, and instant recovery of databases with SQL Safe Backup
Additional SQL Migration Resources:
To learn more about some potential problems and their solutions to migrating to the cloud, please take some time to check out this 4-page whitepaper “Problems and Solutions for Migrating Databases to the Cloud” to understand why migrating database instances to the cloud is a complex task. You must develop the right strategy with proper optimization, planning, and monitoring throughout the migration.