Learn about SQL Server to Azure migration and follow these procedures for a trouble-free migration process
If you’re thinking about migrating your SQL Server to Azure SQL Database, there are some distinct advantages, including:
- Lower cost by shifting expenses from a capital expenditure model to an operational expenditure model
- Improved security with default encryption plus access threat protection
- Improved scalability and high availability
- Ease of maintenance, including automatic backup and recovery and cloud-based disaster recovery
We’ve provided step-by-step instructions below on how to successfully migrate SQL Server databases to Azure SQL Database using the recommended Microsoft tools for small to medium and large databases. Let’s get started.
How to prepare for migrating SQL Server to Azure SQL Database
When you’re thinking about migrating your SQL Server, preparation is key. Correct 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
Here are a couple of 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.
- For Azure DMS, an offering in Microsoft’s Azure Migrate hub, consult the prerequisites in Microsoft’s documentation.
Pre-Migration
Pre-migration identifies any potential issues that could block migration and helps resolve them before migration. Microsoft calls this the assessment phase. This can be conducted 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. 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.
Migrate SQL Server to Azure: Step-by-Step
Migration is 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
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 data migration in two phases. Once you use a test copy of the database and a second time on a production copy (assuming that you find 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 (also 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.
Migrate SQL Server to Azure: 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 is needed. Once you determine 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 to 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 with sample queries against the source and target data and confirm the results.
- Run performance tests to analyze results against the migrated data and compare 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 additional 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.
Additional SQL Migration Resources
I hope these migration procedures have been helpful, for questions please reach out, our team is always happy to assist. We have many solutions for SQL Server, Azure SQL Database, and Amazon RDS for SQL Server. We can help with:
- 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
To learn more about potential problems and solutions to migrating to the cloud, please check out this 4-page whitepaper “Problems and Solutions for Migrating Databases to the Cloud”
Other blogs that might interest you: