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!
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr