When using the Data Migration Assistant you’ll have an
option to export the findings as CSV or JSON.
The following provides a guide to importing the JSON data into a database table for you to review.
Firstly, create a table to hold the information using:
CREATE TABLE DMA_Findings
(
[Project_Status] nvarchar(128)
,[Project_Name] nvarchar(128)
,[Project_SourcePlatform] nvarchar(128)
,[Project_TargetPlatform] nvarchar(128)
,[ServerInstances_ServerName] nvarchar(128)
,[ServerInstances_Version] nvarchar(128)
,[ServerInstances_Status] nvarchar(128)
,[Databases_ServerName] nvarchar(128)
,[Databases_Name] nvarchar(128)
,[Databases_CompatibilityLevel] nvarchar(128)
,[Databases_SizeMB] decimal (20,2)
,[Databases_Status] nvarchar(128)
,[Databases_ServerVersion] nvarchar(128)
,[Databases_ServerEdition] nvarchar(128)
,[AssessmentRecommendations_CompatibilityLevel] nvarchar(128)
,[AssessmentRecommendations_Category] nvarchar(128)
,[AssessmentRecommendations_Severity] nvarchar(128)
,[AssessmentRecommendations_ChangeCategory] nvarchar(128)
,[AssessmentRecommendations_RuleId] nvarchar(128)
,[AssessmentRecommendations_Title] nvarchar(160)
,[AssessmentRecommendations_Impact] nvarchar(4000)
,[AssessmentRecommendations_Recommendation] nvarchar(4000)
,[AssessmentRecommendations_MoreInfo] nvarchar(4000)
,[ImpactedObjects_Name] nvarchar(128)
,[ImpactedObjects_ObjectType] nvarchar(128)
,[ImpactedObjects_ImpactDetail] nvarchar(4000)
,[ImpactedObjects_SuggestedFixes] nvarchar(4000)
);
Next, import a single DMA json file using:
INSERT INTO DMA_Findings(
[Project_Status]
,[Project_Name]
,[Project_SourcePlatform]
,[Project_TargetPlatform]
,[ServerInstances_ServerName]
,[ServerInstances_Version]
,[ServerInstances_Status]
,[Databases_ServerName]
,[Databases_Name]
,[Databases_CompatibilityLevel]
,[Databases_SizeMB]
,[Databases_Status]
,[Databases_ServerVersion]
,[Databases_ServerEdition]
,[AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
,[ImpactedObjects_SuggestedFixes]
)
SELECT
Project.[Status] AS [Project_Status]
,Project.[Name] AS [Project_Name]
,Project.[SourcePlatform] AS [Project_SourcePlatform]
,Project.[TargetPlatform] AS [Project_TargetPlatform]
,[ServerInstances].[ServerName] AS [ServerInstances_ServerName]
,[ServerInstances].[Version] AS [ServerInstances_Version]
,[ServerInstances].[Status] AS [ServerInstances_Status]
--,[ServerInstances].[AssessmentRecommendations] AS [ServerInstances_AssessmentRecommendations]
,[Databases].[ServerName] AS [Databases_ServerName]
,[Databases].[Name] AS [Databases_Name]
,[Databases].[CompatibilityLevel] AS [Databases_CompatibilityLevel]
,[Databases].[SizeMB] AS [Databases_SizeMB]
,[Databases].[Status] AS [Databases_Status]
,[Databases].[ServerVersion] AS [Databases_ServerVersion]
,[Databases].[ServerEdition] AS [Databases_ServerEdition]
,[AssessmentRecommendations].[CompatibilityLevel] AS [AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations].[Category] AS [AssessmentRecommendations_Category]
,[AssessmentRecommendations].[Severity] AS [AssessmentRecommendations_Severity]
,[AssessmentRecommendations].[ChangeCategory] AS [AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations].[RuleId] AS [AssessmentRecommendations_RuleId]
,[AssessmentRecommendations].[Title] AS [AssessmentRecommendations_Title]
,[AssessmentRecommendations].[Impact] AS [AssessmentRecommendations_Impact]
,[AssessmentRecommendations].[Recommendation] AS [AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations].[MoreInfo] AS [AssessmentRecommendations_MoreInfo]
,[ImpactedObjects].[Name] AS [ImpactedObjects_Name]
,[ImpactedObjects].[ObjectType] AS [ImpactedObjects_ObjectType]
,[ImpactedObjects].[ImpactDetail] AS [ImpactedObjects_ImpactDetail]
,[ImpactedObjects].[SuggestedFixes] AS [ImpactedObjects_SuggestedFixes]
FROM
OPENROWSET(BULK N'C:\pathtoyourJSONFile\yourfile.json', SINGLE_CLOB) AS json
OUTER APPLY OPENJSON(BulkColumn)
WITH (
[Status] nvarchar(128)
,[Name] nvarchar(128)
,[SourcePlatform] nvarchar(128)
,[TargetPlatform] nvarchar(128)
,[Databases] nvarchar(MAX) AS JSON
,[ServerInstances] nvarchar(MAX) AS JSON
) AS [Project]
OUTER APPLY OPENJSON([ServerInstances])
WITH (
[ServerName] nvarchar(128)
,[Version] nvarchar(128)
,[Status] nvarchar(128)
--,[AssessmentRecommendations] nvarchar(4000)
) AS [ServerInstances]
OUTER APPLY OPENJSON([Databases])
WITH (
[ServerName] nvarchar(128)
,[Name] nvarchar(128)
,[CompatibilityLevel] nvarchar(128)
,[SizeMB] decimal (20,2)
,[Status] nvarchar(128)
,[ServerVersion] nvarchar(128)
,[ServerEdition] nvarchar(128)
,[AssessmentRecommendations] nvarchar(MAX) AS JSON
) AS [Databases]
OUTER APPLY OPENJSON([AssessmentRecommendations])
WITH (
[CompatibilityLevel] nvarchar(128)
,[Category] nvarchar(128)
,[Severity] nvarchar(128)
,[ChangeCategory] nvarchar(128)
,[RuleId] nvarchar(128)
,[Title] nvarchar(160)
,[Impact] nvarchar(4000)
,[Recommendation] nvarchar(4000)
,[MoreInfo] nvarchar(4000)
,[ImpactedObjects] nvarchar(MAX) AS JSON
) AS [AssessmentRecommendations]
OUTER APPLY OPENJSON([ImpactedObjects])
WITH (
[Name] nvarchar(128)
,[ObjectType] nvarchar(128)
,[ImpactDetail] nvarchar(4000)
,[SuggestedFixes] nvarchar(4000)
) AS [ImpactedObjects];
Or, if you have lots of DMA json files you can quickly load them all using the following PowerShell
$dma_json_folder = 'C:\....\' # Location of the the DMA json files
$instance_name = '...' # SQL instance which will contain the findings
$database_destination = '...' # Database on the SQL instance
# Get a list of the audit queries
$dma_json_files = @(Get-ChildItem $dma_json_folder *.json) # Get a list of audit queries
if ($dma_json_files.Length -ne 0) # Proceed as queries to run
{
try
{
Foreach ($dma_json_file in $dma_json_files) # Cycle through each query
{
[string]$json_file = $dma_json_file.BaseName + '.json' # Set output file name
$query = @"
INSERT INTO DMA_Findings(
[Project_Status]
,[Project_Name]
,[Project_SourcePlatform]
,[Project_TargetPlatform]
,[ServerInstances_ServerName]
,[ServerInstances_Version]
,[ServerInstances_Status]
,[Databases_ServerName]
,[Databases_Name]
,[Databases_CompatibilityLevel]
,[Databases_SizeMB]
,[Databases_Status]
,[Databases_ServerVersion]
,[Databases_ServerEdition]
,[AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
,[ImpactedObjects_SuggestedFixes]
)
SELECT
Project.[Status] AS [Project_Status]
,Project.[Name] AS [Project_Name]
,Project.[SourcePlatform] AS [Project_SourcePlatform]
,Project.[TargetPlatform] AS [Project_TargetPlatform]
,[ServerInstances].[ServerName] AS [ServerInstances_ServerName]
,[ServerInstances].[Version] AS [ServerInstances_Version]
,[ServerInstances].[Status] AS [ServerInstances_Status]
--,[ServerInstances].[AssessmentRecommendations] AS [ServerInstances_AssessmentRecommendations]
,[Databases].[ServerName] AS [Databases_ServerName]
,[Databases].[Name] AS [Databases_Name]
,[Databases].[CompatibilityLevel] AS [Databases_CompatibilityLevel]
,[Databases].[SizeMB] AS [Databases_SizeMB]
,[Databases].[Status] AS [Databases_Status]
,[Databases].[ServerVersion] AS [Databases_ServerVersion]
,[Databases].[ServerEdition] AS [Databases_ServerEdition]
,[AssessmentRecommendations].[CompatibilityLevel] AS [AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations].[Category] AS [AssessmentRecommendations_Category]
,[AssessmentRecommendations].[Severity] AS [AssessmentRecommendations_Severity]
,[AssessmentRecommendations].[ChangeCategory] AS [AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations].[RuleId] AS [AssessmentRecommendations_RuleId]
,[AssessmentRecommendations].[Title] AS [AssessmentRecommendations_Title]
,[AssessmentRecommendations].[Impact] AS [AssessmentRecommendations_Impact]
,[AssessmentRecommendations].[Recommendation] AS [AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations].[MoreInfo] AS [AssessmentRecommendations_MoreInfo]
,[ImpactedObjects].[Name] AS [ImpactedObjects_Name]
,[ImpactedObjects].[ObjectType] AS [ImpactedObjects_ObjectType]
,[ImpactedObjects].[ImpactDetail] AS [ImpactedObjects_ImpactDetail]
,[ImpactedObjects].[SuggestedFixes] AS [ImpactedObjects_SuggestedFixes]
FROM
OPENROWSET(BULK N'$dma_json_folder$json_file', SINGLE_CLOB) AS json
OUTER APPLY OPENJSON(BulkColumn)
WITH (
[Status] nvarchar(128)
,[Name] nvarchar(128)
,[SourcePlatform] nvarchar(128)
,[TargetPlatform] nvarchar(128)
,[Databases] nvarchar(MAX) AS JSON
,[ServerInstances] nvarchar(MAX) AS JSON
) AS [Project]
OUTER APPLY OPENJSON([ServerInstances])
WITH (
[ServerName] nvarchar(128)
,[Version] nvarchar(128)
,[Status] nvarchar(128)
--,[AssessmentRecommendations] nvarchar(4000)
) AS [ServerInstances]
OUTER APPLY OPENJSON([Databases])
WITH (
[ServerName] nvarchar(128)
,[Name] nvarchar(128)
,[CompatibilityLevel] nvarchar(128)
,[SizeMB] decimal (20,2)
,[Status] nvarchar(128)
,[ServerVersion] nvarchar(128)
,[ServerEdition] nvarchar(128)
,[AssessmentRecommendations] nvarchar(MAX) AS JSON
) AS [Databases]
OUTER APPLY OPENJSON([AssessmentRecommendations])
WITH (
[CompatibilityLevel] nvarchar(128)
,[Category] nvarchar(128)
,[Severity] nvarchar(128)
,[ChangeCategory] nvarchar(128)
,[RuleId] nvarchar(128)
,[Title] nvarchar(160)
,[Impact] nvarchar(4000)
,[Recommendation] nvarchar(4000)
,[MoreInfo] nvarchar(4000)
,[ImpactedObjects] nvarchar(MAX) AS JSON
) AS [AssessmentRecommendations]
OUTER APPLY OPENJSON([ImpactedObjects])
WITH (
[Name] nvarchar(128)
,[ObjectType] nvarchar(128)
,[ImpactDetail] nvarchar(4000)
,[SuggestedFixes] nvarchar(4000)
) AS [ImpactedObjects];
"@
#
Write-Host "Processing" $dma_json_folder$json_file
# Use Invoke-Sqlcmd to extract the information
Invoke-Sqlcmd -Query $query -ServerInstance $instance_name -Database $database_destination -QueryTimeout 600 -ErrorAction Stop
}
}
catch
{
Write-Host 'SQL query error: ' $_.Exception.Message -ForegroundColor Red
#Exit; # Exit and go no further
}
}
Now you’ll
be able to query the findings:
SELECT * FROM [DMA_Findings];
An example of finding issues for a migration to SQL Server 2017 you could use a query like:
SELECT
[Databases_ServerName] AS Instance
,[Databases_ServerEdition] AS InstanceEdition
,[ServerInstances_Version] AS InstanceBuild
,[Project_SourcePlatform] AS SourcePlatform
,[Project_TargetPlatform] AS TargetPlatform
,[Databases_Name] AS DatabaseName
,[Databases_CompatibilityLevel] AS DatabaseCompatibilityLevel
,[AssessmentRecommendations_CompatibilityLevel] AS TargetCompatibilityLevel
,[Databases_SizeMB] AS Database_SizeMB
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
FROM [DMA_Findings]
WHERE 1=1
AND [AssessmentRecommendations_CompatibilityLevel] = 'CompatLevel140'
ORDER BY [Databases_ServerName],[Databases_Name],[AssessmentRecommendations_Severity],[AssessmentRecommendations_ChangeCategory];
