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:
- Select report type
- Select source server, databases, and tables
- 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!
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar