How to consolidate Assessment Reports


Starting from Data Migration Assistant (DMA) v 2.1, DMA provides command-line support for running assessments in unattended mode.  This feature helps to run the assessments at scale.  The command-line version of DMA generates the assessment results in the form of a JSON or CSV file.

You can assess multiple databases in a single instantiation of DMA command line utility and export the all the assessments results into a single JSON file or assess one database at time and later consolidate the results from these multiple JSON files into a SQL database.

Check out the following link on how to run the DMA tool from command line:  Data Migration Assistant: How to run from command line

Use the attached PowerShell script to import the assessment results from JSON files into a SQL Server database.

Using the PowerShell script:

You will need to provide the following information upon script execution:

  • serverName:  SQL Server instance name that you want to import the assessment results from JSON files.
  • databaseName:  The database name that the results gets imported to
  • jsonDirectory:  The folder that the assessment results saved in one or more JSON files.
  • processTo:  SQLServer

Supply the above parameter values in the PowerShell script in the "EXECUTE FUNCTIONS" section as it appears below.

dmaProcessor -serverName localhost `
-databaseName DMAReporting `
-jsonDirectory "C:\temp\DMACmd\output\" `
-processTo SQLServer

The PowerShell script creates the following objects in the SQL instance you have specified if they don’t already exist:

  • Database – The name provided in the PowerShell parameters
    • Main repository
  • Table – ReportData
    • Data for reporting
  • Table - BreakingChangeWeighting
    • Reference table for all breaking changes.  Here you can define your own weighting values to influence a more accurate % upgrade success ranking
  • View – UpgradeSuccessRanking_OnPrem
    • View displaying a success factor for each database to be migrated on premise
  • View – UpgradeSuccessRanking_Azure
    • View displaying a success factor for each database to be migrated on premise
  • Stored Procedure – JSONResults_Insert
    • Used to import data from JSON file into SQL Server
  • Stored Procedure – AzureFeatureParityResults_Insert
    • Used to import Azure feature parity results from JSON file into SQL Server
  • Table Type – JSONResults
    • Used to hold the JSON results for on premise assessments and passed into the JSONResults_Insert stored procedure
  • Table Type – AzureFeatureParityResults
    • Used to hold the Azure feature parity results for azure assessments and passed into the AzureFeatureParityResults_Insert stored procedure

The PowerShell script will create a “Processed” directory inside the directory you provided which contains the JSON files that are to be processed.

Once the script completes, the results are imported into the table ReportData.

Viewing the Results in SQL Server

Once the data has been loaded, connect up to your SQL Server instance.  You should see the following:

 

image

 

The dbo.ReportData table contains the contents of the JSON file in it’s raw form.

On Premise Upgrade Success Ranking

To see a list of databases and their % success rank, select from the dbo.UpgradeSuccessRanking_OnPrem view:

image

 

Here we can see for a given database what the upgrade success chance is split by greater compatibility levels.  So for example, HR was assessed against compatibility level 100, 110, 120 and 130.  This helps you visually see how much effort is involved in migrating to a greater version of SQL Server to the one the database is currently on.

Usually the metric we care about is how many breaking changes there are for a given database.  In the above example we can see that the HR database has a 50% upgrade success factor.

This metrics can be influenced by altering the weighting values in the dbo.BreakingChangeWeighting table.

For example, I've decided that the effort involved in fixing the syntax issue in the HR database is quite high so I've assigned a value of 3, it wouldn’t take long to fix so I've assigned a value of 1 and there would be some cost involved in making the change so I've assigned a value of 2.  This changes the blended Changerank to 2.

Note:  The scoring is on a scale of 1-5.  1 being low 5 being high.  Also note that ChangeRank is a computed column.

image

 

Now when I query the dbo.UpgradeSuccessRanking_OnPrem view my upgrade success factor for Breaking Changes now drops:

image

 

Azure Upgrade Success Ranking

Similar to the on premise ranking, to see a list of databases to migrate to Azure SQL DB and their % success rank, select from the dbo.UpgradeSuccessRanking_Azure view:

image

Here we are interested in the MigrationBlocker value.  100.00 means that there is a 100% success rank for moving this database to Azure SQL Database V12.

The difference with this view is that there is currently no override for changing the weighting for migration blocker rules.

To review a method of reporting on this data set using PowerBi checkout this blog post.

Get the script here

dma_processor_v5-0

Learn more

Data Migration Assistant Blog

Comments (4)

  1. Sean S says:

    A very helpful script.

    Thanks!

  2. Hiram says:

    PS C:\Deployments\DMA> dmaProcessor -serverName localhost `
    >> -databaseName DMAReporting `
    >> -jsonDirectory “C:\Deployments\DMA\” `
    >> -processTo SQLServer
    Database DMAReporting created successfully
    Table ReportData created successfully
    Table AzureFeatureParity created successfully
    Table BreakingChangeWeighting created successfully
    Exception calling “Create” with “0” argument(s): “Create failed for View ‘dbo.DatabaseCategoryRanking’. ”
    At line:209 char:9
    + $vwDatabaseCategoryRanking.Create()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

    View DatabaseCategoryRanking created successfully
    Exception calling “Create” with “0” argument(s): “Create failed for View ‘dbo.UpgradeSuccessRanking’. ”
    At line:289 char:9
    + $vwUpgradeSuccessRanking.Create()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

    View UpgradeSuccessRanking created successfully
    Exception calling “Create” with “0” argument(s): “Create failed for View ‘dbo.UpgradeSuccessRanking_OnPrem’. ”
    At line:363 char:9
    + $vwUpgradeSuccessRankingop.Create()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

    View UpgradeSuccessRanking_OnPrem created successfully
    Exception calling “Create” with “0” argument(s): “Create failed for View ‘dbo.UpgradeSuccessRanking_Azure’. ”
    At line:441 char:9
    + $vwUpgradeSuccessRankingaz.Create()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

    View UpgradeSuccessRanking_Azure created successfully
    Table Type JSONResults created successfully
    Table Type AzureFeatureParityResults created successfully
    Stored Procedure JSONNResults_Insert created successfully
    Stored Procedure AzureFeatureParityResults_Insert created successfully

    Directory: C:\Deployments\DMA

    Mode LastWriteTime Length Name
    —- ————- —— —-
    d—– 7/25/2017 11:40 AM Processed
    Processed directory created successfully at []
    0
    Rows Processed for ReportData Table = 2606 Rows processed for AzureFeatureParityTable = 0 for file C:\Deployments\DMA\DMAReport.json Total Processing
    Time = 00:00:00.8964059

    PS C:\Deployments\DMA>
    PS C:\Deployments\DMA>
    PS C:\Deployments\DMA>
    PS C:\Deployments\DMA> # To process on a named instance use SERVERNAME\INSTANCENAME as the -serverName
    PS C:\Deployments\DMA>
    PS C:\Deployments\DMA> #———————————————————————————— END EXECUTE FUNCTIONS ——————-
    —————————————————————-
    PS C:\Deployments\DMA>
    PS C:\Deployments\DMA>

    1. Views created via t-sql successfully. Ended up making an edit to views to better present the data.

      –:connect LPROSPECT010

      USE DMAReporting
      GO

      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      /*[DatabaseCategoryRanking]*/
      CREATE VIEW [dbo].[DatabaseCategoryRanking] AS
      WITH DatabaseRanking
      AS
      (
      SELECT [Name]
      ,ChangeCategory
      ,COUNT(*) AS “NumberOfIssues”
      ,(CONVERT(NUMERIC(5,2),COUNT(*))/(SELECT CONVERT(NUMERIC(5,2),COUNT(*)) FROM reportdata r2 Where r1.[name] = r2.[name])) * 100 AS “ChangeCategoryPercentage”
      FROM reportdata r1
      GROUP BY [Name], ChangeCategory
      )
      SELECT [Name] AS “DatabaseName”
      ,ChangeCategory
      ,ChangeCategoryPercentage
      FROM DatabaseRanking;
      GO

      /*[UpgradeSuccessRanking]*/
      CREATE VIEW [dbo].[UpgradeSuccessRanking] AS

      WITH issuecount
      AS
      (
      — currently doesn’t take into account diminishing returns for repeating issues
      — removed NotDefined as these are for feature parity, not migration blockers and should therefore be excluded in calculations
      SELECT InstanceName
      ,NAME
      ,TargetCompatibilityLevel
      ,COALESCE(CASE changecategory WHEN ‘BehaviorChange’ THEN COUNT(*) END,0) AS ‘BehaviorChange’
      ,COALESCE(CASE changecategory WHEN ‘Deprecated’ THEN COUNT(*) END,0) AS ‘DeprecatedCount’
      ,COALESCE(CASE changecategory WHEN ‘BreakingChange’ THEN SUM(ChangeRank) END ,0) AS ‘BreakingChange’
      –,COALESCE(CASE changecategory WHEN ‘NotDefined’ THEN COUNT(*) END,0) AS ‘NotDefined’
      ,COALESCE(CASE changecategory WHEN ‘MigrationBlocker’ THEN COUNT(*) END,0) AS ‘MigrationBlocker’
      FROM reportdata rd
      LEFT JOIN BreakingChangeWeighting bcw
      ON rd.RuleId = bcw.ruleid
      WHERE ChangeCategory NOT IN ( ‘NotDefined’,’Features’) –HF
      and TargetCompatibilityLevel != ‘NA’
      GROUP BY InstanceName,name, changecategory, TargetCompatibilityLevel
      ),
      distinctissues
      AS
      (
      SELECT InstanceName
      ,NAME
      ,TargetCompatibilityLevel
      ,MAX(BehaviorChange) AS ‘BehaviorChange’
      ,MAX(DeprecatedCount) AS ‘DeprecatedCount’
      ,MAX(BreakingChange) AS ‘BreakingChange’
      –,MAX(NotDefined) AS ‘NotDefined’
      ,MAX(MigrationBlocker) AS ‘MigrationBlocker’
      FROM issuecount
      GROUP BY InstanceName,name, TargetCompatibilityLevel
      ),
      IssueTotaled
      AS
      (
      SELECT *, behaviorchange + deprecatedcount + breakingchange + MigrationBlocker AS ‘Total’
      FROM distinctissues
      ),
      RankedDatabases
      AS
      (
      SELECT InstanceName
      ,Name
      ,TargetCompatibilityLevel
      ,CAST(100-((BehaviorChange + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘BehaviorChange’
      ,CAST(100-((DeprecatedCount + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘DeprecatedCount’
      ,CAST(100-((BreakingChange + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘BreakingChange’
      –,CAST(100-((NotDefined + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘NotDefined’
      ,CAST(100-((MigrationBlocker + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘MigrationBlocker’
      FROM IssueTotaled
      )
      — This section will ensure that if there are 0 issues in a category we return 1. This ensures the reports show data
      SELECT InstanceName
      ,[Name]
      ,TargetCompatibilityLevel
      ,CASE WHEN BehaviorChange > 0 THEN BehaviorChange ELSE 1 END AS “BehaviorChange”
      ,CASE WHEN DeprecatedCount > 0 THEN DeprecatedCount ELSE 1 END AS “DeprecatedCount”
      ,CASE WHEN BreakingChange > 0 THEN BreakingChange ELSE 1 END AS “BreakingChange”
      –,CASE WHEN NotDefined > 0 THEN NotDefined ELSE 1 END AS “NotDefined”
      ,CASE WHEN MigrationBlocker > 0 THEN MigrationBlocker ELSE 1 END AS “MigrationBlocker”
      FROM RankedDatabases;
      GO

      /*[UpgradeSuccessRanking_OnPrem]*/
      CREATE VIEW [dbo].[UpgradeSuccessRanking_OnPrem] AS
      WITH issuecount
      AS
      (
      — currently doesn’t take into account diminishing returns for repeating issues
      — removed NotDefined as these are for feature parity, not migration blockers and should therefore be excluded in calculations
      SELECT InstanceName
      ,NAME
      ,TargetCompatibilityLevel
      ,COALESCE(CASE changecategory WHEN ‘BehaviorChange’ THEN COUNT(*) END,0) AS ‘BehaviorChange’
      ,COALESCE(CASE changecategory WHEN ‘Deprecated’ THEN COUNT(*) END,0) AS ‘DeprecatedCount’
      ,COALESCE(CASE changecategory WHEN ‘BreakingChange’ THEN SUM(ChangeRank) END ,0) AS ‘BreakingChange’
      FROM ReportData rd
      LEFT JOIN BreakingChangeWeighting bcw
      ON rd.RuleId = bcw.ruleid
      WHERE ChangeCategory NOT IN ( ‘NotDefined’,’Features’) –HF
      AND TargetCompatibilityLevel != ‘NA’
      AND AssessmentTarget IN (‘SqlServer2012’, ‘SqlServer2014’, ‘SqlServer2016’)
      GROUP BY InstanceName,name, changecategory, TargetCompatibilityLevel
      ),
      distinctissues
      AS
      (
      SELECT InstanceName
      ,NAME
      ,TargetCompatibilityLevel
      ,MAX(BehaviorChange) AS ‘BehaviorChange’
      ,MAX(DeprecatedCount) AS ‘DeprecatedCount’
      ,MAX(BreakingChange) AS ‘BreakingChange’
      FROM issuecount
      GROUP BY InstanceName,name, TargetCompatibilityLevel
      ),
      IssueTotaled
      AS
      (
      SELECT *, behaviorchange + deprecatedcount + breakingchange AS ‘Total’
      FROM distinctissues
      ),
      RankedDatabases
      AS
      (
      SELECT InstanceName
      ,Name
      ,TargetCompatibilityLevel
      ,CAST(100-((BehaviorChange + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘BehaviorChange’
      ,CAST(100-((DeprecatedCount + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘DeprecatedCount’
      ,CAST(100-((BreakingChange + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘BreakingChange’
      FROM IssueTotaled
      )
      — This section will ensure that if there are 0 issues in a category we return 1. This ensures the reports show data
      SELECT InstanceName
      ,[Name]
      ,TargetCompatibilityLevel
      ,CASE WHEN BehaviorChange > 0 THEN BehaviorChange ELSE 1 END AS “BehaviorChange”
      ,CASE WHEN DeprecatedCount > 0 THEN DeprecatedCount ELSE 1 END AS “DeprecatedCount”
      ,CASE WHEN BreakingChange > 0 THEN BreakingChange ELSE 1 END AS “BreakingChange”
      FROM RankedDatabases;
      GO

      /*[UpgradeSuccessRanking_Azure]*/
      CREATE VIEW [dbo].[UpgradeSuccessRanking_Azure] AS
      WITH issuecount
      AS
      (
      — currently doesn’t take into account diminishing returns for repeating issues
      — removed NotDefined as these are for feature parity, not migration blockers and should therefore be excluded in calculations
      SELECT InstanceName
      ,NAME
      ,TargetCompatibilityLevel
      ,COALESCE(CASE changecategory WHEN ‘BehaviorChange’ THEN COUNT(*) END,0) AS ‘BehaviorChange’
      ,COALESCE(CASE changecategory WHEN ‘Deprecated’ THEN COUNT(*) END,0) AS ‘DeprecatedCount’
      ,COALESCE(CASE changecategory WHEN ‘BreakingChange’ THEN SUM(ChangeRank) END ,0) AS ‘BreakingChange’
      ,COALESCE(CASE changecategory WHEN ‘MigrationBlocker’ THEN COUNT(*) END,0) AS ‘MigrationBlocker’
      FROM ReportData rd
      LEFT JOIN BreakingChangeWeighting bcw
      ON rd.RuleId = bcw.ruleid
      WHERE ChangeCategory NOT IN ( ‘NotDefined’,’Features’) –HF
      AND TargetCompatibilityLevel != ‘NA’
      AND AssessmentTarget = ‘AzureSQLDatabaseV12’
      GROUP BY InstanceName, [Name], changecategory, TargetCompatibilityLevel
      ),
      distinctissues
      AS
      (
      SELECT InstanceName
      ,[Name]
      ,TargetCompatibilityLevel
      ,MAX(BehaviorChange) AS ‘BehaviorChange’
      ,MAX(DeprecatedCount) AS ‘DeprecatedCount’
      ,MAX(BreakingChange) AS ‘BreakingChange’
      ,MAX(MigrationBlocker) AS ‘MigrationBlocker’
      FROM issuecount
      GROUP BY InstanceName, [Name], TargetCompatibilityLevel
      ),
      IssueTotaled
      AS
      (
      SELECT *, behaviorchange + deprecatedcount + breakingchange + MigrationBlocker AS ‘Total’
      FROM distinctissues
      ),
      RankedDatabases
      AS
      (
      SELECT InstanceName
      ,[Name]
      ,TargetCompatibilityLevel
      ,CAST(100-((BehaviorChange + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘BehaviorChange’
      ,CAST(100-((DeprecatedCount + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘DeprecatedCount’
      ,CAST(100-((BreakingChange + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘BreakingChange’
      ,CAST(100-((MigrationBlocker + 0.00) / (total + 0.00)) * 100 AS DECIMAL(5,2)) AS ‘MigrationBlocker’
      FROM IssueTotaled
      )
      — This section will ensure that if there are 0 issues in a category we return 1. This ensures the reports show data
      SELECT InstanceName
      ,[Name]
      ,TargetCompatibilityLevel
      ,CASE WHEN BehaviorChange > 0 THEN BehaviorChange ELSE 1 END AS “BehaviorChange”
      ,CASE WHEN DeprecatedCount > 0 THEN DeprecatedCount ELSE 1 END AS “DeprecatedCount”
      ,CASE WHEN BreakingChange > 0 THEN BreakingChange ELSE 1 END AS “BreakingChange”
      ,CASE WHEN MigrationBlocker > 0 THEN MigrationBlocker ELSE 1 END AS “MigrationBlocker”
      FROM RankedDatabases;
      GO

  3. Hi all, i´m trying to maintain this tool with the following repository
    https://github.com/enriquecatala/DataMigrationAssistantHelper
    Cheers!

Skip to main content