Run Data Migration Assistant from the command line

Artikel
06/28/2024

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"

Dela via

Run Data Migration Assistant from the command line

Assessments using the Command Line Interface (CLI)

Examples of assessments using the CLI

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

Examples of SKU assessments using the CLI

Feedback

Ytterligare resurser

Dela via

Run Data Migration Assistant from the command line

Assessments using the Command Line Interface (CLI)

Examples of assessments using the CLI

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

Examples of SKU assessments using the CLI

Related content

Feedback

Ytterligare resurser