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...
If you’re thinking about migrating your SQL Server to Azure SQL Database, there are some distinct advantages, including:
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.
When you’re thinking about migrating your SQL Server, preparation is key. Correct preparation starts with:
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.
Here are a couple of migration tools, their purpose, and the supported SQL Server versions.
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) 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).
You should address the following prerequisite tool options before proceeding with 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.
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.
Migrating schema comprises the following steps:
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:
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 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.
A good migration test plan should include the following elements:
Remediate any application that consumes the source data or any problems in migrated data that may have surfaced from testing.
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.
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:
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:
database-management
Brandon Adams - 14 May
database-management
Brandon Adams - 2 October
database-management
Brandon Adams - 30 September
Contact our sales team to get a personalized demo of our database management software for SQL Server!
