Overview of Data Migration Assistant (DMA)

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 supported sources include any SQL Server with version 2005 and later. The supported target versions are SQL Server 2012 and later, as well as Azure SQL Database (single instance), Azure SQL Managed Instance and SQL Server running on an Azure Virtual Machine.

There are two types of projects – assessment and migration.

Assessment

The assessment project supports three report types. The first one checks the database compatibility. It discovers migration blocking issues and deprecated features by analysing databases you choose as your source server.

The second report type checks for feature parity. It discovers unsupported or partially supported features and functions that your application may rely on. Guidance around areas that may need some re-engineering is provided, as well.

The last one is still in a ‘coming soon’ phase. It is intended to provide about benefits from new features. This assessment discovers new SQL Database features that are applicable to your source databases once migrated.

The assessment project is a three steps process where you:

1. Select report type
2. Select source server, databases, and tables
3. Review the assessment report

Below is an example of an assessment report where the source is SQL Server 2019, and the target is Azure SQL Database.

Migration

The migration project is a six steps process, which begins with connecting to the source and the target destination. Then you can select all or some of the schema objects. For example, you can only migrate the schemas, or selected tables, stored procedures, views, logins, etc. Once the objects are selected, SQL script is generated accordingly. You can review the script and make changes if needed.

Lastly, you can select the tables containing data you would like to migrate and start the migration.

In conclusion, the DMA is a great tool for small database migrations, and it is user-friendly, which makes it suitable for non-IT people. However, there are some limitations. While there is no hard limit on the exact number of tables that could be migrated, the DMA is dependent on your machine’s resources. For this reason, Azure Data Migration Service (DMS) is the preferred tool for large database migrations. It supports online and offline migrations and provides better scaling and resiliency build in. Also, DMA do not support copy of objects like SQL Server Agent Jobs, Linked Servers, and others. Find the tool’s best practices here.

If you would like to learn more about data migration and how Adatis can support your organisation, we would love to chat. Get in touch today or email enquiries@adatis.co.uk. You can also follow Adatis on LinkedIn to make sure you don’t miss any blogs!