SQL Server Data Migration Assistant

Question

Hello, sorry for not being up to speed nowadays but I used to use Upgrade Advisor to check for code incompatibilities check when migrating upgrade to newer SQL Server versions.

In my case here am migrating upgrading from SQL Server 2014 to SQL Server 2019.

Believe SQL Server Data Migration Assistant is now the replacement...is it true that its resource intensive and be careful when running for checks?

Can I install it on Sever_Dev and have it point to different Server_Prod to do the checks?...(would this be more recommended than installing/running it on Prod server?)

Can it check throughout all SQL Agent jobs as well for code incompatibilities?

Thanks in advance.

Answer 1

Hi,

I believe yes you can use it to target the remote SQL servers and analyse the DBs for consistency and compatibility issues, also check this guide over here - https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver16

The best practices advice you to not install on the SQL server and also run this off peak hours to prevent performance issues - https://learn.microsoft.com/en-us/sql/dma/dma-bestpractices?source=recommendations&view=sql-server-ver16

Hope this helps.

JS

==

Please Accept the answer if the information helped you. This will help us and others in the community as well.

Answer 2

Hi @techresearch7777777 ,

The SQL Server Data Migration Assistant (DMA) is now the recommended tool for checking code compatibility when upgrading to a newer version of SQL Server. DMA is designed to help you identify issues that can affect database functionality after you upgrade, and it provides specific recommendations for how to resolve those issues.

DMA is a resource-intensive tool, as it has to analyze the entire schema and codebase of your database to identify potential compatibility issues. It's recommended to use a dedicated server with sufficient resources to run DMA checks. However, you can also install it on your development environment and point it to the production server to do the checks.

It's generally recommended to run DMA checks on a test or development environment rather than the production environment. This is because DMA can modify the schema and objects on the target database during the analysis process. Running it on a production environment could potentially cause unexpected downtime or data loss.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Microsoft provides the SQL Server Data Migration Assistant (DMA), a free tool designed to assist organizations and database administrators assess and migrate on-premises SQL Server databases to either newer versions of SQL Server or Azure SQL Database, seamlessly. DMA serves as an invaluable asset when planning or executing database migration projects; providing insights into potential issues as well as solutions.

Here are the primary features and functionalities of SQL Server Data Migration Assistant:

Assessment Reports: DMA can conduct a detailed examination of your existing SQL Server databases and generate assessment reports to assist with understanding any compatibility issues that might occur when migrating to newer versions of SQL Server or Azure SQL Database.

Compatibility Issues: DMA can quickly identify any compatibility issues in your database schema, code and queries which could prevent an effortless migration experience. It provides detailed information for each issue to make solving them simpler.

Recommendations: DMA offers recommendations and best practices for resolving identified issues, which can help mitigate problems before starting the migration process.

Schema and Data Migration: Once DMA has assessed your database, it can assist in migrating both schema and data to its target environment. DMA supports various migration scenarios including on-premises to on-premises migrations, Azure migrations, as well as cross-platform moves.

Command-Line Support: DMA provides a command-line interface (CLI) which facilitates automation of assessment and migration tasks, making it suitable for integration into automated deployment pipelines.

Integrate With Azure Services: DMA can assist in migrating to Azure by offering suggestions for Azure SQL Database deployment options and help create scripts to deploy it on its servers.

Incremental Migrations: DMA offers incremental migration capabilities, allowing you to take advantage of any updates to the source database after initial migration to keep your target database up-to-date throughout. This ensures a successful migration experience.

Reporting and Logging: DMA offers comprehensive reporting and logging features, which allow you to monitor the progress of your migration project while troubleshooting any issues that arise along the way.

Overall, SQL Server Data Migration Assistant is an indispensable tool for planning, assessing and executing database migrations - whether upgrading to a newer version of SQL Server or transitioning your database to Azure SQL Database in the cloud. It helps facilitate an efficient transition while mitigating compatibility issues or any potential downtime during migration processes.