The Data Migration Assistant is a great tool developed to assess your SQL Server environment for a migration to a modern SQL Server platform such as SQL Server 2017 or Azure SQL Database. If you’re assessing a single SQL Server with only a few databases, reading the exported results (which can be in .JSON or .CSV format) may not be that difficult. But if you’re assessing dozens of environments including hundreds of databases, we need a better way to understand the results.Recently a customer reached out to me to help with the challenging task of understanding the assessment results of 61 SQL environments including over 500 databases being considered for migrating to Azure SQL Database. Now there is already a great solution that exists for aggregating DMA assessment exports but it only works for assessment exports in .JSON format. The existing solution also requires that the assessment results be written to a SQL Server database.
So I built a solution that uses Power BI to parse the DMA assessment exports (.CSV format) and aggregate the data so it can be more effectively browsed and understood in a report. There are two different templates to use depending on if your assessing your SQL environment for migration to Azure SQL or SQL Server, so make sure you download the right file seen below.
Download the Assessment Analysis for Azure SQL template here.
Download the Assessment Analysis for SQL Server template here.
The difference between the two templates is that the Assessment Analysis for Azure SQL template includes a report page breaking down the feature parity information output from DMA. The Assessment Analysis for SQL Server template does not include this information so make sure you use the right template depending on what you’re doing with your SQL environment.
The Power BI templates have one parameter used to specify the location of the assessment files. When you open the template file you’ll be asked to specify the directory where the assessments are located. Keep in mind that your assessments should be in a .csv format for use with this template. Also, this template only works for assessments for migrating to Azure SQL DB but I’ll be updating it for assessments to upgrade to modern versions SQL Server.
Make sure that the AssessmentDirectory parameter ends with a slash, as seen above. Once you provide the AssessmentDirectory parameter value, click Load and your reports will light up. Below you can see the reports that are included with the template, but you can create any other reports you might need based on the dataset.
I hope that you find this useful for simplifying your migration to a modern SQL Server platform. Let me know if you found this useful or if you have a question by leaving a comment down below! Thanks for reading!
Resources
Download the DMA Assessment Analysis for Azure SQL template file
Download the DMA Assessment Analysis for SQL Server template file
Download the Data Migration Assistant to perform a migration assessment of your environment
Great template anyway,
but “Feature Parity” will only be checked if I move to Azure.
Thus, if I assess e.g. moving from SQL Server 2012 to SQL Server 2017 the template will produce a lot of errors due to lines like
#”Filtered Rows2″ = Table.SelectRows(#”Added Index1″, each ([Column1] = “;””Feature Parity”””)),
in the functions.
Good point, Frank. I’ll make a note of that in the post.
Frank, I added an additional template file that does not include Feature Parity for those looking to migrate to SQL Server as opposed to Azure SQL DB. Take a look and let me know what you think.
Did you create one for assessments to upgrade to modern versions SQL Server? Was trying to figure out why I was getting an error trying to use the template and just realized is only for Azure upgrades.
I just posted it! Take a look at the updated blog post to get the latest template. After you test it out, let me know what you think. Thanks!