Edit

Share via

Run Data Migration Assistant from the command line

  • Article

Important

Data Migration Assistant (DMA) is deprecated. For migration options from SQL Server to Azure SQL, see the migration options for SQL Server to Azure SQL.

With version 2.1 and later versions, when you install Data Migration Assistant, it also installs dmacmd.exe in %ProgramFiles%\Microsoft Data Migration Assistant. Use DMACMD to assess your databases in an unattended mode, and output the result to JSON or CSV file. This method is especially useful when assessing several databases or huge databases.

DMACMD supports running assessments only. Migrations aren't supported at this time.

Assessments using the Command Line Interface (CLI)

dmacmd.exe /AssessmentName="string"
/AssessmentDatabases="connectionString1" ["connectionString2"]
[/AssessmentSourcePlatform="SourcePlatform"]
[/AssessmentTargetPlatform="TargetPlatform"]
/AssessmentEvaluateFeatureParity|/AssessmentEvaluateCompatibilityIssues
[/AssessmentOverwriteResult]
/AssessmentResultJson="file"|/AssessmentResultCsv="file"
Argument Description Required (Y/N)
/help or /? How to use dmacmd.exe help text N
/AssessmentName Name of the assessment project Y
/AssessmentDatabases Space-delimited list of connection strings. Database name (Initial Catalog) is case-sensitive. Y
/AssessmentSourcePlatform Source platform for the assessment:
Supported values for Assessment: SqlOnPrem, RdsSqlServer (default)
Supported values for Target Readiness Assessment: SqlOnPrem, RdsSqlServer (default), Cassandra (preview)		 N
/AssessmentTargetPlatform Target platform for the assessment:
Supported values for Assessment: AzureSqlDatabase, ManagedSqlServer, SqlServer2012, SqlServer2014, SqlServer2016, SqlServerLinux2017, and SqlServerWindows2017 (default)
Supported values for Target Readiness Assessment: ManagedSqlServer (default), CosmosDB (preview)		 N
/AssessmentEvaluateFeatureParity Run feature parity rules. If source platform is RdsSqlServer, feature parity evaluation isn't supported for target platform AzureSqlDatabase Y
(Either AssessmentEvaluateCompatibilityIssues or AssessmentEvaluateFeatureParity is required.)
/AssessmentEvaluateCompatibilityIssues Run compatibility rules Y
(Either AssessmentEvaluateCompatibilityIssues or AssessmentEvaluateFeatureParity is required.)
/AssessmentOverwriteResult Overwrite the result file N
/AssessmentResultJson Full path to the JSON result file Y
(Either AssessmentResultJson or AssessmentResultCsv is required)
/AssessmentResultCsv Full path to the CSV result file Y
(Either AssessmentResultJson or AssessmentResultCsv is required)
/AssessmentResultDma Full path to the .dma result file N
/Action Use SkuRecommendation to get SKU recommendations.
Use AssessTargetReadiness to perform target readiness assessment.
Use AzureMigrateUpload to upload all DMA assessment files in the AssessmentResultInputFolder to bulk upload to Azure Migrate. Action type usage /Action=AzureMigrateUpload		 N
/SourceConnections Space delimited list of connection strings. Database name (Initial Catalog) is optional. If no database name is provided, then all databases on the source are assessed. Y
(Required if Action is AssessTargetReadiness)
/TargetReadinessConfiguration Full path to the XML file describing values for the name, source connections, and result file. Y
(Either TargetReadinessConfiguration or SourceConnections is required)
/FeatureDiscoveryReportJson Path to the feature discovery JSON report. If this file is generated, then it can be used to run target readiness assessment again without connecting to source. N
/ImportFeatureDiscoveryReportJson Path to the feature discovery JSON report created earlier. Instead of source connections, this file is used. N
/EnableAssessmentUploadToAzureMigrate Enables uploading and publishing assessment results to Azure Migrate N
/AzureCloudEnvironment Selects the Azure cloud environment to connect to, default is Azure Public Cloud. Supported values: Azure (default), AzureChina, AzureGermany, AzureUSGovernment. N
/SubscriptionId Azure subscription ID. Y
(Required if EnableAssessmentUploadToAzureMigrate argument is specified)
/AzureMigrateProjectName The Azure Migrate Project name to upload assessment results to. Y
(Required if EnableAssessmentUploadToAzureMigrate argument is specified)
/ResourceGroupName Azure Migrate resource group name. Y
(Required if EnableAssessmentUploadToAzureMigrate argument is specified)
/AssessmentResultInputFolder The input folder path containing .dma assessment files to upload to Azure Migrate. Y
(Required if Action is AzureMigrateUpload)

Examples of assessments using the CLI

DMACMD

dmacmd.exe /?

Or:

dmacmd.exe /help`

Single-database assessment using Windows authentication and running compatibility rules

dmacmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;Integrated Security=true"
/AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult
/AssessmentResultJson="C:\\temp\\Results\\AssessmentReport.json"

Single-database assessment using SQL Server authentication and running feature parity

dmacmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;User Id=myUsername;Password=myPassword;"
/AssessmentEvaluateFeatureParity /AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\Results\\AssessmentReport.csv"

Single-database assessment for target platform SQL Server 2012, save results to .json and .csv file

dmacmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;Integrated Security=true"
/AssessmentTargetPlatform="SqlServer2012"
/AssessmentEvaluateFeatureParity /AssessmentOverwriteResult
/AssessmentResultJson="C:\\temp\\Results\\AssessmentReport.json"
/AssessmentResultCsv="C:\\temp\\Results\\AssessmentReport.csv"

Single-database assessment for target platform Azure SQL Database, save results to .json and .csv file

dmacmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;Integrated Security=true"
/AssessmentTargetPlatform="AzureSqlDatabaseV12"
/AssessmentEvaluateCompatibilityIssues /AssessmentEvaluateFeatureParity
/AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\AssessmentReport.csv"
/AssessmentResultJson="C:\\temp\\AssessmentReport.json"

Multiple-database assessment

dmacmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName1;Initial
Catalog=DatabaseName1;Integrated Security=true"
"Server=SQLServerInstanceName1;Initial Catalog=DatabaseName2;Integrated
Security=true" "Server=SQLServerInstanceName2;Initial
Catalog=DatabaseName3;Integrated Security=true"
/AssessmentTargetPlatform="SqlServer2016"
/AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\Results\\AssessmentReport.csv"
/AssessmentResultJson="C:\\Results\\test2016.json"

Single-database Target Readiness assessment using Windows authentication

dmacmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/SourceConnections="Server=SQLServerInstanceName;Initial Catalog=DatabaseName;Integrated Security=true"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"

Single-database Target Readiness assessment using SQL Server authentication

dmacmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/SourceConnections="Server=SQLServerInstanceName;Initial Catalog=DatabaseName;User Id=myUsername;Password=myPassword;" /AssessmentEvaluateFeatureParity
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"

Single-database assessment for target platform Azure SQL Database, save results to .json and .csv file

dmacmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;Integrated Security=true"
/AssessmentSourcePlatform="SqlOnPrem"
/AssessmentTargetPlatform="AzureSqlDatabase"
/AssessmentEvaluateCompatibilityIssues /AssessmentEvaluateFeatureParity
/AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\AssessmentReport.csv"
/AssessmentResultJson="C:\\temp\\AssessmentReport.json"

Multiple-database Target Readiness assessment

dmacmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/AssessmentSourcePlatform=SourcePlatform
/AssessmentTargetPlatform=TargetPlatform
/SourceConnections="Server=SQLServerInstanceName1;Initial Catalog=DatabaseName1;Integrated Security=true" "Server=SQLServerInstanceName1;Initial Catalog=DatabaseName2;Integrated Security=true" "Server=SQLServerInstanceName2;Initial Catalog=DatabaseName3;Integrated Security=true"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\Results\test2016.json"

(/AssessmentSourcePlatform and /AssessmentTargetPlatform are optional.)

Target Readiness assessment for all databases on a server using Windows authentication

dmacmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/SourceConnections="Server=SQLServerInstanceName;Integrated Security=true"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"

Target Readiness assessment by importing feature discovery report created earlier

dmacmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/ImportFeatureDiscoveryReportJson="c:\temp\feature_report.json"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"

Target Readiness assessment by providing configuration file

dmacmd.exe /Action=AssessTargetReadiness
/TargetReadinessConfiguration=.\Config.xml

Configuration file contents when using source connections:

<?xml version="1.0" encoding="utf-8" ?>
<TargetReadinessConfiguration xmlns="http://microsoft.com/schemas/SqlServer/Advisor/TargetReadinessConfiguration">
  <AssessmentName>name</AssessmentName>
  <SourcePlatform>Source Platform</SourcePlatform> <!-- Optional. The default is SqlOnPrem -->
  <TargetPlatform>TargetPlatform</TargetPlatform> <!-- Optional. The default is ManagedSqlServer -->
  <SourceConnections>
    <SourceConnection>connection string 1</SourceConnection>
    <SourceConnection>connection string 2</SourceConnection>
    <!-- ... -->
    <SourceConnection>connection string n</SourceConnection>
  </SourceConnections>
  <AssessmentResultJson>path\to\file.json</AssessmentResultJson>
  <FeatureDiscoveryReportJson>path\to\featurediscoveryreport.json</FeatureDiscoveryReportJson>
  <OverwriteResult>true</OverwriteResult> <!-- or false -->
</TargetReadinessConfiguration>

Configuration file contents when importing feature discovery report:

<TargetReadinessConfiguration xmlns="http://microsoft.com/schemas/SqlServer/Advisor/TargetReadinessConfiguration">
  <AssessmentName>name</AssessmentName>
  <ImportFeatureDiscoveryReportJson>path\to\featurediscoveryfile.json</ImportFeatureDiscoveryReportJson>
  <AssessmentResultJson>path\to\resultfile.json</AssessmentResultJson>
  <OverwriteResult>true</OverwriteResult><!-- or false -->
</TargetReadinessConfiguration>

Assess and upload to Azure Migrate in Azure Public Cloud (default)

dmacmd.exe
/Action="Assess"
/AssessmentSourcePlatform=SqlOnPrem
/AssessmentTargetPlatform=ManagedSqlServer
/AssessmentEvaluateCompatibilityIssues
/AssessmentEvaluateFeatureParity
/AssessmentOverwriteResult
/AssessmentName="assess-myDatabase"
/AssessmentDatabases="Server=myServer;Initial Catalog=myDatabase;Integrated Security=true"
/AssessmentResultDma="C:\assessments\results\assess-1.dma"
/SubscriptionId="Subscription Id"
/AzureMigrateProjectName="Azure Migrate project ame"
/ResourceGroupName="Resource Group name"
/AzureAuthenticationInteractiveAuthentication
/AzureAuthenticationTenantId="Azure Tenant Id"
/EnableAssessmentUploadToAzureMigrate

Batch upload DMA assessment files to Azure Migrate in Azure Public Cloud (default)

dmacmd.exe
/Action="AzureMigrateUpload"
/AssessmentResultInputFolder="C:\assessments\results"
/SubscriptionId="Subscription Id"
/AzureMigrateProjectName="Azure Migrate project name"
/ResourceGroupName="Resource Group name"
/AzureAuthenticationInteractiveAuthentication
/AzureAuthenticationTenantId="Azure Tenant Id"
/EnableAssessmentUploadToAzureMigrate

Azure SQL Database / Azure SQL Managed Instance / SQL Server on Azure VM SKU recommendations using the CLI

With version 5.4 and later versions, when you install Data Migration Assistant, it also installs SqlAssessment.exe in %ProgramFiles%\Microsoft Data Migration Assistant\SQLAssessmentConsole. Use SqlAssessment.exe to collect performance data for your SQL instance over an extended period of time, and output the result to JSON or CSV file.

These commands support recommendations for both Azure SQL Database single database, Azure SQL Managed Instance and SQL Server on Azure VM deployment options.

.\SqlAssessment.exe GetSkuRecommendation
--outputFolder C:\Output
--targetPlatform AzureSqlManagedInstance
Argument Description Required (Y/N)
PerfDataCollection Starts collection of performance data. Y
GetSkuRecommendation Performs aggregation and analysis of the collected performance data and determines SKU recommendations. Y
GetMetadata Performs a metadata collection of the target SQL instances, including the number and properties of server instances, databases and database files, user-defined objects, etc. A full report is exported to MetadataReport.json. Y
--outputFolder Folder which performance data, reports, and logs are written to/read from. N
(Default: current directory)
--sqlConnectionStrings Quote-enclosed formal connection strings for the target SQL instances. Y
--overwrite Whether or not to overwrite any existing assessment or SKU recommendations reports. N
(Default: true)
--perfQueryIntervalInSec Interval at which to query performance data, in seconds. N
(Specific for PerfDataCollection action. Default 30)
--staticQueryIntervalInSec Interval at which to query and persist static configuration data, in seconds. N
(Specific for PerfDataCollection action. Default 30)
--numberOfIterations Number of iterations of performance data collection to perform before persisting to file. N
(Specific for PerfDataCollection action. Default 20)
--perfQueryIntervalInSec Interval at which performance data was queried, in seconds. N
(Specific for GetSkuRecommendation action. This must match the value that was originally used during the performance data collection. Default: 30)
--targetPlatform Target platform for SKU recommendation: either AzureSqlDatabase, AzureSqlManagedInstance, AzureSqlVirtualMachine, or Any. N
(Specific for GetSkuRecommendation action. Default: Any)
--targetSqlInstance Name of the SQL instance that SKU recommendation is targeting. N
(Specific for GetSkuRecommendation action)
--targetPercentile Percentile of data points to be used during aggregation of the performance data. N
(Specific for GetSkuRecommendation action. Only used for baseline (nonelastic) strategy. Default: 95)
--scalingFactor Scaling (comfort) factor used during SKU recommendation. N
(Specific for GetSkuRecommendation action. Default: 100)
--startTime UTC start time of performance data points to consider during aggregation, in "YYYY-MM-DD HH:MM" format. N
(Specific for GetSkuRecommendation action. Only used for baseline (nonelastic) strategy)
--endTime UTC end time of performance data points to consider during aggregation, in "YYYY-MM-DD HH:MM" format N
(Specific for GetSkuRecommendation action. Only used for baseline (nonelastic) strategy)
--elasticStrategy Whether or not to use the elastic strategy for SKU recommendations based on statistical resource usage profiling. Elastic strategy is currently available for Azure SQL Databases and SQL Managed Instance, not yet available for SQL Server on Azure VM target. N
(Specific for GetSkuRecommendation action. Default: false)
--databaseAllowList Space separated list of names of databases to be included for SKU recommendations N
(Specific for GetSkuRecommendation action. Default: null)
--databaseDenyList Space separated list of names of databases to be excluded for SKU recommendations. Only set one of the following or neither: databaseAllowList, databaseDenyList N
(Specific for GetSkuRecommendation action. Default: null)
--displayResult Whether or not to print the SKU recommendation results to the console. Only set one of the following or neither: databaseAllowList, databaseDenyList N
(Specific for GetSkuRecommendation action. Default: true)

Examples of SKU assessments using the CLI

SqlAssessment.exe

SqlAssessment.exe --help

Start the data collection process for on-premises SQL Server instances

.\SqlAssessment.exe PerfDataCollection
--sqlConnectionStrings "Data Source=Server1;Initial Catalog=master;Integrated Security=True;" "Data Source=Server2;Initial Catalog=master;Integrated Security=True;"
--outputFolder C:\Output

Azure SQL Database / Azure SQL Managed Instance / SQL Server on Azure VM SKU recommendations

.\SqlAssessment.exe GetSkuRecommendation
--outputFolder C:\Output
--targetPlatform Any

Azure SQL Managed Instance SKU recommendations with specific aggregation percentage for data points and custom scaling factor

.\SqlAssessment.exe GetSkuRecommendation
--outputFolder C:\Output
--targetPlatform AzureSqlManagedInstance
--targetPercentile 90
--scalingFactor 80

SQL Server on Azure VM SKU recommendations with custom aggregation timeline

.\SqlAssessment.exe GetSkuRecommendation
--outputFolder C:\Output
--targetPlatform AzureSqlVirtualMachine
--startTime "2021-06-05 00:00"
--endTime "2021-06-07 00:00"

Related content