Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Data Migration Assistant for SQL Server Upgrades

I recently took advantage of an opportunity to try Mirosoft's Data Migration Assistant. It was a good experience and I found the tool quite useful. As the documentation tells us, the 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." For my use case, I wanted to assess a SQL 2008 R2 environment with more than a hundred user databases for an on-premises upgrade to SQL 2017.

There's not much to the Welcome screen. Simply click the + button as indicated on the left side toolbar.

Dave Mason - SQL Server - Data Migration Assistant

Pick a Project Type (Assessment in this example), enter a Project Name, and the Target server type. Note the four options

  • Azure SQL Database
  • Azure SQL Database Managed Instance
  • SQL Server on Azure Virtual Machine
  • SQL Server

Dave Mason - SQL Server - Data Migration Assistant

Clicking next brings us to the Options page. Choices here will vary slightly, based on the Target server type. Note the target versions listed--the oldest supported target version is SQL 2012. But since SQL 2008 and SQL 2008 R2 fall out of support soon, that's fine by me. For the report type, I was only interested in Compatibility issues.

Dave Mason - SQL Server - Data Migration Assistant

Dave Mason - SQL Server - Data Migration Assistant

On the next screen, specify the SQL instance to connect to, and one or more databases to assess. (If you want to assess databases from more than one instance, additional sources can be added by clicking the "Add sources" toolbar button/icon.) Here, I specified a named instance on my local machine, and chose 3 of the five user databases.

Dave Mason - SQL Server - Data Migration Assistant

Dave Mason - SQL Server - Data Migration Assistant

Review the choices and click Back to change them, or click Start Assessment. I'll go with the latter.

Dave Mason - SQL Server - Data Migration Assistant

There are some animated gif-like graphics while the assessment is running. By default, the app will use up to eight threads/connections (this behavior can be controlled by a parameter in the dma.exe.config file).

Dave Mason - SQL Server - Data Migration Assistant

After the DMA finishes assessing all the databases, a report of findings is displayed. All of the compatibility issues discovered will be displayed here. Click on a database to show what issues were found. Issues are broken out into the following types:

  • Breaking changes
  • Behavior changes
  • Deprecated features
  • Information issues

Dave Mason - SQL Server - Data Migration Assistant

Clicking/selecting a specific issue shows the Issue details, Impacted objects, and Recommended Fix(es). In the example here, there were only two issues found: unqualified joins and deprecated data types. There are tabs for multiple versions of SQL Server, from 140 (SQL Server 2017) downward. These show the impact of each issue, depending on what compatibility level the database would be placed in after an upgrade or migration. That both issues are present across the board tells us we can't make either issue "go away" by running the Northwind database on SQL 2017 in any of the lower compatibility levels.

Northwind, Pubs, and XYZ databases didn't turn up much. Since they didn't, here's a handful of other issues I found in another database:

  • Information
    • Remove references to undocumented system tables.
  • Behavior Change
    • SET ROWCOUNT used in the context of DML statements such as INSERT, UPDATE, or DELETE.
    • Full-Text Search has changed since SQL Server 2008.
    • ORDER BY specifies integer ordinal.
  • Breaking Change
    • CLR assemblies marked as SAFE or EXTERNAL_ACCESS are considered UNSAFE.
    • Constant expressions are not allowed in the ORDER BY clause in 90 or later compatibility modes.


Assessment Report

You can export a report to either a *.csv or *.json file. I didn't find either that helpful (I created a *.csv report file and opened, edited, and saved it as an Excel spreadsheet), although either can be ingested by Power BI.

Dave Mason - SQL Server - Data Migration Assistant


Feedback

The little smiley icon in the lower left is for user feedback. I clicked it and submitted a few items. Along the way, I also encountered an error with three databases that prevented the assessment from completing. I ended up contacting Microsoft via email. To my surprise, I received a response in less than an hour. They surmised there was a stored procedure in each database that caused the error. Removing it enabled the assessment to complete successfully. Thanks, Microsoft!

Dave Mason - SQL Server - Data Migration Assistant

Comments

Post comment