DMACMD: Assess readiness of a SQL Server data estate migrating to Azure SQL

With many organizations trying to migrate to Azure, it is critical to assess existing on-premises SQL Server instances and identify the right Azure SQL target - Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure VMs.

Data Migration Assistant (DMA) helps assess a SQL Server instance for a specific Azure SQL target, and gauges the readiness of SQL Server databases migrating to Azure SQL. Upload DMA assessment results to Azure Migrate hub for a centralized readiness view of the entire data estate.

This article teaches you to perform assessments at scale and upload the results to Azure Migrate hub using the DMA command-line interface (DMACMD). Alternatively, you can use the DMA GUI to perform the assessment instead.

To learn more, see the following Channel9 video:

Prerequisites

To use DMACMD to perform an assessment and upload the results to Azure Migrate hub, you need the following:

• The latest version of Data Migration Assistant (DMA).
• An Azure Migrate project.
• Contributor role access to the Azure Migrate project resource.

Use DMACMD

Use an XML file as input to run assessments at scale using the command-line interface (DMACMD.exe).

Use the following sample command to pass an XML file to DMACMD and start the assessment:

C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe /Action=Assess /AssessmentConfiguration= C:\Demo\ScaleAssessment\Assess-for-AzureSQLMI.xml

The contents of sample Assess-for-AzureSQLMI.xml define the elements to assess SQL Server instances for a SQL Managed Instance target:

<?xml version="1.0" encoding="UTF-8"?>
<AssessmentConfiguration xmlns="http://microsoft.com/schemas/SqlServer/Advisor/AssessmentConfiguration">
   <AssessmentName>Scale-Assessment-for-AzureSQLManagedInstance</AssessmentName>
   <AssessmentSourcePlatform>SqlOnPrem</AssessmentSourcePlatform>
   <AssessmentTargetPlatform>ManagedSqlServer</AssessmentTargetPlatform>
   <AssessmentDatabases>
      <AssessmentDatabase>Server=ServerName\SQL2017;Integrated Security=true</AssessmentDatabase>
      <AssessmentDatabase>Server=ServerName\SQL2016;Integrated Security=true;Initial Catalog=AdventureWorks2022</AssessmentDatabase>
      <AssessmentDatabase>Server=ServerName\SQL2016;Integrated Security=true;Initial Catalog=TestDB</AssessmentDatabase>
   </AssessmentDatabases>
   <AssessmentResultDma>C:\Demo\ScaleAssessment\AssessmentConfiguration\Scale-Assessment-for-AzureSQLManagedInstance.dma</AssessmentResultDma>
   <AssessmentResultJson>C:\Demo\ScaleAssessment\AssessmentConfiguration\Scale-Assessment-for-AzureSQLManagedInstance.json</AssessmentResultJson>
   <AssessmentResultCsv>C:\Demo\ScaleAssessment\AssessmentConfiguration\Scale-Assessment-for-AzureSQLManagedInstance.csv</AssessmentResultCsv>
   <AssessmentOverwriteResult>true</AssessmentOverwriteResult>
   <AssessmentEvaluateCompatibilityIssues>true</AssessmentEvaluateCompatibilityIssues>
   <AssessmentEvaluateFeatureParity>true</AssessmentEvaluateFeatureParity>
   <AzureCloudEnvironment>Azure</AzureCloudEnvironment>
   <SubscriptionId>xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx</SubscriptionId>
   <AzureMigrateProjectName>Scale-Assessment-for-AzureSQLMI</AzureMigrateProjectName>
   <ResourceGroupName>Resource-Group-Name</ResourceGroupName>
   <AzureAuthenticationInteractiveAuthentication>true</AzureAuthenticationInteractiveAuthentication>
   <AzureAuthenticationTenantId>xxxxxxxx-xxxx-xxxxxxxx</AzureAuthenticationTenantId>
   <EnableAssessmentUploadToAzureMigrate>true</EnableAssessmentUploadToAzureMigrate>
</AssessmentConfiguration>

XML Elements

Note

While Microsoft Entra ID is the new name for Azure Active Directory (Azure AD), to prevent disrupting existing environments, Azure AD still remains in some hardcoded elements such as UI fields, connection providers, error codes, and cmdlets. In this article, the two names are interchangeable.

The XML elements that are passed to DMACMD are defined in the following table:

XML Element	Definition
AssessmentName	The name of the assessment
AssessmentSourcePlatform	Source SQL Server platform. The default value is SqlOnPrem.
AssessmentTargetPlatform	Target SQL Server platform. AzureSqlDatabase is for an Azure SQL Database target. ManagedSqlServer is for an Azure SQL Managed Instance target. The sample Assess-for-AzureSQLMI assess a SQL Managed Instance target.
AssessmentDatabases	If you need to assess all the databases in an instance, then specify just the instance name else list specific databases in each line. The sample Assess-for-AzureSQLMI assess all databases in instance Servername\SQL2017 and two specific databases in instance Servername\SQL2016.
AssessmentResultDma AssessmentResultJson AssessmentResultCsv	Specifies the format of the result file. .DMA, .JSON, and .CSV respectively. Double-click .DMA to open in the DMA UI. AssessmentResultDma is required to upload assessment results to Azure Migrate hub.
AssessmentOverwriteResult	Indicates whether to overwrite any existing assessment result file with the same path as AssessmentResultJson, AssessmentResultDma or AssessmentResultCsv.
AssessmentEvaluateCompatibilityIssues AssessmentEvaluateFeatureParity	Perform assessment to evaluate compatibility issues and feature parity issues respectively.
AzureCloudEnvironment	Azure cloud environment to connect to, default is Azure Public Cloud. Supported values: Azure (default), AzureChina, AzureGermany, AzureUSGovernment.
SubscriptionId	Azure subscription ID.
AzureMigrateProjectName	Azure Migrate project name to upload assessment results to.
ResourceGroupName	Azure Migrate resource group name.
AzureAuthenticationInteractiveAuthentication	Set to true to pop up the authentication window.
AzureAuthenticationTenantId	Microsoft Entra tenant ID. Obtain this from the Overview pane of Microsoft Entra ID in the Azure portal.
EnableAssessmentUploadToAzureMigrate	Set to true to upload and publish assessment results to Azure Migrate hub.

Results

DMACMD outputs a status when it finishes successfully.

The following is a sample result output:

Assessment finished for project: Scale-Assessment-for-AzureSQLManagedInstance
DATABASES:
Succeeded             : 4
Failed                : 0
SERVER INSTANCES:
Succeeded             : 2
Failed                : 0
CSV result file       : C:\Demo\ScaleAssessment\Scale-Assessment-for-AzureSQLManagedInstance.csv
JSON result file      : C:\Demo\ScaleAssessment\Scale-Assessment-for-AzureSQLManagedInstance.json
--------------------------------------------------------------------------------

View uploaded results in Azure Migrate for a centralized view of the entire data estate.

Best practices

Consider the following best practices when using DMACMD:

• Logically group together target SQL Server instances and databases based on the application, rather than assessing all SQL Server instances in the entire data estate.
• Create a separate Azure Migrate project for each Azure SQL target to avoid overwriting results.
• The time to run an assessment depends on the number of database objects. If possible, avoid running assessments on production system and offload to a virtual machine or staging server instead, especially for databases with a large number of objects.

See also

• Data Migration Assistant (DMA)
• Data Migration Assistant: Configuration settings
• Data Migration Assistant: Best Practices