Working with clients, I see instances of SQL Server 2008 and 2012 still being used in production. Extended support ended for 2008 and 2008R2 on July 9, 2019. Mainstream support for SQL Server 2012 ended on January 9, 2019 with extended support ending on July 12, 2022. In addition to end of support, improvements have been made starting in SQL Server 2016 the dramatically improve the stability and performance of the SQL Server Engine. Because Azure SQL Server is running the most current version of SQL Server, moving to Azure SQL Single Database or Managed Instance provides these benefits.
Microsoft has provided tools to assess an existing instance or database for compatibility with Azure SQL and newer versions of SQL Server. In this post, I will talk about the Data Migration Assistant (DMA). This is the first of three blog posts about the DMA
The DMA is available for free download from Microsoft. From Microsoft’s documentation:
The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
The DMA contains a GUI interface that will assess SQL Server databases for upgrade the following versions of SQL Server:
- SqlServer2012
- SqlServer2014
- SqlServer2016
- SqlServerWindows2017
- SqlServerLinux2017
- SqlServerWindows2019
- SqlServerLinux2019
It also will assess SQL Server databases for migration to these destinations in Azure:
- Azure SQL Single Database
- Azure SQL Managed Instance
For instances to be upgraded to a newer version of SQL Server, the DMA will flag breaking changes, behavior changes, and deprecated features. For instances to be migrated to Azure, the DMA will flag migration blockers and unsupported or partially supported features. The DMA can be run against multiple instances and individual databases can be excluded from the assessment.
The assessment report contains an instance-level feature parity tab and a database-level compatibility issues tab. The database-level compatibility tab shows compatibility for a range of compatibility level setting. For migration to Azure SQL, these range from 120 to 150. For upgrade to a newer version, these range from 100 to 150.
Once an assessment is run, it can be saved in a .dma file for later analysis in the DMA. The results can be exported to a .json file. Finally, they results can be uploaded to the Azure Migrate service.
The post is an introduction to the features of the DMA. In the next post, I will introduce PowerShell functions available from Microsoft that allow automation of DMA assessments.
Continue here for Part 2.