Data Migration Assistant: Configuration settings

Certain behavior of Data Migration Assistant (DMA) could be tweaked or fine tuned using configuration values from dma.exe.config file. Here are few key configuration values.

System_CAPS_ICON_note.jpg Note
  • You can find the configuration file at 
    • %ProgramFiles%\Microsoft Data Migration Assistant\dma.exe.config, if using DMA desktop application
    • %ProgramFiles%\Microsoft Data Migration Assistant\dmacmd.exe.config if using DMA command line utility
  • Please save a copy of the original config file before any modification.
  • Please restart DMA for the new configuration values to take effect.

Number of databases to assess in parallel

DMA assesses multiple databases in parallel. During assessment DMA extracts data-tier application (dacpac) to understand the database schema. This operation could time-out if several databases on the same server are assessed in parallel. With DMA v2.0 this could be controlled using the below configuration value. Default value is 8.

<assessment parallelDatabases="8" />

Number of databases to migrate in parallel

DMA migrates multiple databases in parallel, before migrating logins. During migration DMA will take a backup of the source database, optionally copy the backup, and then restore it on the target server. You may encounter timeout failures when several databases are selected for migration. With DMA v2.0, if you experience this problem you can reduce the below configuration value. On the other hand, increase the value to reduce the overall migration time.

<migration parallelDatabases=”8″ />

DacFX settings

During assessment DMA extracts data-tier application (dacpac) to understand the database schema. This operation could fail with time-outs for extremely large databases, or if server is under load. In such a case above configuration values could be modified to avoid errors, starting with DMA v1.0. Entire <dacfx> entry is commented by default. Please uncomment the entry and then modify the value as needed.

  • commandTimeout: This sets the IDbCommand.CommandTimeout property in seconds. (Default=60)
  • databaseLockTimeout: This is equivalent to SET LOCK_TIMEOUT timeout_period in milliseconds. (Default=5000)
  • maxDataReaderDegreeOfParallelism: Number of Sql connection pool connections to use. (Default=8)

<dacFx  commandTimeout="60" databaseLockTimeout="5000" maxDataReaderDegreeOfParallelism="8"/>

Stretch Database feature recommendation threshold

SQL Server Stretch Database lets you dynamically stretch warm and cold transactional data from Microsoft SQL Server 2016 to Azure. Stretch Database targets transactional databases with large amounts of cold data. Stretch Database recommendation under Storage feature recommendation , first identifies tables that it thinks will benefit from stretch, and then identifies changes that need to be made to enable the table for stretch. With DMA v2.0 this threshold for a table to qualify for stretch could controlled using the above configuration value. DEfault value is 100,000 rows. However if you want to analyze the stretch capabilities for even smaller tables then lower the value accordingly.

<stretchDBAdvisor  recommendedNumberOfRows="100000" />

SQL connection timeout

SQL connection time-out to source and target instances while running assessment or migration can now be controlled using the below connection timeout setting in seconds. Default is 15 secs

<add key="ConnectionTimeout" value="15" />

Number of parallel tasks to use in data migration

Number of parallel tasks to create during data migration to Azure SQL Database. When commented-out, DMA will choose the number of parallel tasks based on the number of cores on your machine and the service objective of your target database (read more about this setting).

<appSettings> <add key="QueryTableDataRangeTaskCount" value="4" /> </appSettings>

Max batch size for data migration (KB)

Maximum size of batch (in kilobytes) to create when writing data to target tables during data migration to Azure SQL Database. Default is 262144 (256 * 1024kb, or 256mb).

<appSettings> <add key="MaxBatchSizeKb" value="262144" /> </appSettings>

Bulk copy timeout

Bulk copy timeout (in seconds) to use during data migration to Azure SQL Database. Default (if config file is left unchanged) is 0, which indicates no timeout.

<appSettings> <add key="BulkCopyTimeout" value="0" /> </appSettings>


Comments (0)

Skip to main content