This post is also available in: esEspañol (Spanish)

I have uploaded a repository containing a helper to analyse the Data Migration Assistant tool results to GitHub. I have compiled and improved Microsoft’s version released last March in order to process aggregated results from multiple servers using Microsoft’s static code analysis tool.

It can be found below: https://github.com/enriquecatala/DataMigrationAssistantHelper which is currently compatible with version 3.1 (the latest available at the time of going to press)

How does it work?

Static code analysis and results export

Firstly, you must run the “Data Migration Assistant “and export the results to JSON once the analysis has been completed.

button to export the analysis results to JSON.

This process must be repeated for all SQL Server instances that you want to upgrade, therefore resulting in 1 json file per instance.

IMPORTANT: Please click on the official link below to view a step by step guide https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

Relational DB results consolidation

Since the Data Migration Assistant will not allow you to import the JSON files, as soon as you have closed the application, you will be lost and will no longer be able to view the JSON data. Don’t worry, though, because Microsoft has left us with an “off the record” powershell that can import those json files and import them into a relational DB.

If you want to consolidate all results into a single DB for further analysis, you will simply need to modify the following PowerShell and add the following details (at the end of the script):

Variable Example value
-serverName (local)\sql2016
-databaseName DMAReporting
-jsonDirectory “D:\your_path_to_the_json_files\”
-processTo SQLServer

IMPORTANT: Below is the original version that the github project was based upon: https://blogs.msdn.microsoft.com/datamigration/2017/03/01/how-to-consolidate-json-assessment-reports/

The execution result is a series of tables containing the consolidated data from all existing .json files in the folder linked to in -processTo

Viewing the results using PowerBI

PowerBI provides the best way of analysing the above results, as it will allow you to see all consolidated information in a simple way. In order to view the results:

  1. Install PowerBI desktop if you haven’t done so already
  2. Open the DataMigrationAssistantHelper’s .pbit template
  3. Enter all data concerning the DB where your data was consolidated into and press the “LOAD” button (don’t press ENTER)

You are now ready to go. You will find several analysis tabs containing data based on the target type selected during the migration.

IMPORTANT: If you selected SQL Azure PaaS as your target, you will not be able to see any data on the On Premise tab, and viceversa.

Finally, below are some of the dashboards that you may find:

General incompatibilities

Full results breakdown

Detailed incompatibility results

Issues and solutions

Incompatibilities summary and solutions

And remember that we’re always here to help if you are interested in upgrading your SQL Server. Please contact me

Enrique Catalá

Enrique Catalá es Mentor en SolidQ, Microsoft Data Platform MVP desde 2013 e Ingeniero en informática. Es Microsoft Certified Trainer (MCT) y está centrado en el motor relacional SQL Server, tanto en la resolución de problemas de rendimiento y escalabilidad en sistemas OLTP como la definición e implementación de entornos de alta disponibilidad confiables, en donde ha llevado con éxito más de 100 proyectos no solo en España, sino en diferentes países como EEUU, Holanda, México, Arabia Saudí, Austria... Es el arquitecto principal de las soluciones para SolidQ llamadas HealthCheck, SQL2Cloud, SCODA y del generador de SSIS de SolidQ. Ponente habitual del SolidQ SUMMIT, miembro y ponente en SQL PASS tanto en España como Iberoamérica ponente en varios SQLSaturday