SQL Server Data Migration Assistant

techresearch7777777 1,406 Reputation points
2023-03-18T07:16:17.6233333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,126 questions
Accepted answer
  1. JimmySalian-2011 30,061 Reputation points
    2023-03-18T11:36:35.03+00:00

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 15,186 Reputation points Microsoft Vendor
    2023-03-20T02:23:44.94+00:00

    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".