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:
- Install PowerBI desktop if you haven’t done so already
- Open the DataMigrationAssistantHelper’s .pbit template
- 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
I also works with the definition and implementation of reliable high availability environments, where I have successfully led more than 100 projects not only in Spain but in different countries like USA, Netherlands, Mexico, etc. I am the principal architect of the solutions called HealthCheck, QueryAnalytics and DatabaseObfuscator... I wrote the book "Planning for migration to SQL Server SQL Server 2008 R2" (ISBN : 978-84-936417-9-5).
- Debugging applications against production data: obfuscation and GDPR - November 22, 2019
- SQL Server performance with Spectre and Meltdown patches - January 18, 2018
- Data Migration Assistant Helper - September 18, 2017
