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!
Building Data Apps with Python’s Streamlit
Off-the-shelf solutions for interactive data app development such as Microsoft Power BI are great –
Nov
Power BI to Power AI – Part 3
This post is the third part of a blog series on the AI features of
Nov
Data Factory in Fabric
As data engineers, we use Azure Data Factory on a daily basis to collect datasets
Nov
Microsoft Fabric Is Generally Available
Microsoft Fabric is now Generally Available What has happened? On the 15th November Microsoft announced
Nov
An Introduction to Semantic Link in Microsoft Fabric
Having previously worked in the water & energy industries, I recently created a utilities PowerBI
Nov
Prompt Engineering and Creating a Python Function to Query OpenAI
Artificial intelligence tools, like Chat GPT, are all the buzz right now, and rightfully so
Nov
Exploring different options for collaborative workspaces: Hex, Hyperquery & Deepnote
Real-time collaboration, code completion and data versioning are just a few of the latest capabilities
Nov
NHS Blood and Transplant Shortlisted for Government Project Delivery Awards
We are delighted to share that NHS Blood and Transplant’s data platform project has been
Nov