Released: Data Migration Assistant (DMA) v2.1


Overview

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It also allows you to not only move your schema, data, but also uncontained objects from your source server to your target server.

DMA replaces all previous versions of SQL Server Upgrade Advisor and should be used for upgrades for most SQL Server versions (see below for supported versions).

What is new?

DMA v2.1 is a minor version update and includes following:

Existing features

Supported sources and target versions

Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
Target: SQL Server 2012, SQL Server 2014, and SQL Server 2016

Installation

You can install the tool from Microsoft Download Center. Execute DataMigrationAssistant.msi to install Data Migration Assistant.

Resources

Comments (5)

  1. Andrew Parker says:

    I noticed that for a migration the credentials used for the source db needs CONTROL SERVER permission and for the target db needs sysadmin server role.
    I was attempting to use the DMA tool against a Amazon RDS instance for either the source or target, but I’m not able to because the credentials do not have either of those permissions. I’d really like a mode that doesn’t require such extreme permissions. Maybe a mode that doesn’t transfer logins, or what ever it is that requires such high permissions. Please consider this option in future releases.

    1. Thank you Andrew for the feedback, we will review your suggestion and update you.

  2. FzzyBnny78 says:

    I am trying to use DMA 2.1 to determine the necessary work to migrate existing databases to SQL Server 2016. There are two databases that error out when using DMA with the error (sensitive data was replaced with XXXXX) provided below. The databases are on a SQL Server 2008 R2 instance and set to compatibility level 100. Any ideas?

    The assessment of database ‘XXXXX’ on server ‘XXXXX’ failed.
    Failed to extract dacpac at location ‘C:\Users\XXXXX\AppData\Local\DataMigrationAssistant\DacFxAnalyzer-968db7c742844e47b0-XXXXX\XXXXX.dacpac’.
    Could not extract package from specified database.
    Error 0: An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.ISqlModelElement: Batch Command during reverse engineer failed with Error Code: -2146232060 Line Number: 3 Error Message: Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    1. Hi,

      You will see an error similar to “Incorrect syntax” if DMA can not extract the dacpac from the database. This could happen when the script of an object such as a stored procedure has incorrect syntax. Is the error pasted in your comment the exact error without any scrapping? In most cases the error below also specifies the element that is causing the problem.
      Example: Could not extract package from specified database.
      Error SQL46010: Error validating element [dbo].[colonuse]: Incorrect syntax near table1.
      Error SQL70589: Error validating element [dbo].[test]: NO_LOG and TRUNCATE_ONLY options were removed in SQL Server 2008.

      The correct way to fix this issue is to identify the element and fix the incorrect syntax.
      If you could share some more information with our team at dmafeedback@microsoft.com, we can take a deeper look at the specific issue.

      You could also try to extract dacpac from the database using SQL Server Management Studio refer to Extract a DAC From a Database to identify the element that is causing this issue.

      1. FzzyBnny78 says:

        Akash-

        Thank you for your prompt response. I will investigate further with the information you provided and reach out to DMA Feedback as necessary. Have a great day!

        Alex

Skip to main content