Optimizing Data Migration for Dynamics 365 Finance and Supply Chain Management

Optimizing Data Migration for Dynamics 365 Finance and Supply Chain Management

Data migration is a key component towards a successful Go-Live. One of the main concerns some customers have is the speed in which the data can be migrated, especially if there are vast amounts of data and a small cutover window.

The information below represents a set of steps/actions that can be taken in order to optimise the performance of data migration. 

Note: Testing results on a tier 1 environment should not be compared or extrapolated to performance on a tier 2+ environment.

The standard entities have not all been optimized for data migration, for example some have been optimized for OData integration, therefore if the required entity cannot be optimized to meet the performance requirements it is recommended that a new optimized entity be created. To accelerate this process a developer can duplicate an existing entity.

Begin the optimization phase by using a subset of the data, for example, if it is necessary to import 1,000,000 records, consider starting with 1,000 then increasing to 10,000 and then 100,000.

Once you have identified the entity you will test first:

Disable Change tracking

Data management > Data entities > Change tracking > Disable Change Tracking

 

Enable Set-based processing

Data management > Data entities > Set-based processing

Not all entities support set-based processing and you may receive a message such as:

 

If it is necessary to create an entity that allows set-based processing, some key considerations are:

• The data sources can't be read-only
• The parameter on the data entity ValidTimeStateEnabled must be No
• All data sources must have TableType of Regular
• The parameter of QueryType on the Metadata node can't be Union
• The main data source can't prevent saving data across companies however embedded data sources allow it
• The main data source can't prevent saving data across partitions however embedded data sources allow it

Create data migration batch group

During cutover data migration is a key step during which time little to no other activity is likely to be going on, therefore it is recommended that a batch group is created with all or at least n-1 AOSs assigned.

System administration > Setup > Batch group

 

Priority-based batch scheduling

Currently in preview is a new feature which will optimize the way batch jobs are executed, if contention is identified in the batch framework consider enabling this preview feature, more information is available here.

Maximum batch threads

You can configure the maximum number of threads that can be used for multithreading purposes on each AOS. This value should be changed cautiously, if the number is too high it can have negative performance implications for the environment. The default value is 4 and for most scenarios this should be adequate, if necessary this can be raised to 8. It should not be raised above 8 without significate performance testing to ensure it does not impact the environment.

System administration > Setup > Server configuration

 

Import in batch

Whenever running an import job, ensure that it is run in batch, otherwise it will be run using a single thread which will prevent the system from using the majority of these optimization configurations.

 

Clean staging tables

Clean up the staging tables, this can be achieved by scheduling the Job history cleanup job (available in Platform update 29 and later). After enabling this feature via Feature management you can access it from:

Data management > Job history cleanup

Feature management description:

 

Defragmentation of indexes

Review the configuration of the index defragmentation job. It is recommended that this is run daily though not when a data migration job is executing. Review the parameters to ensure the job captures the tables/indexes that are being changed via the data migration job. Further information on this job can be found here.

Update statistics

Prior to running a data migration job for a large volume of data consider updating the statistics across the associated tables. This is taken care of automatically in production environments. You can run update statistics for a specific table from LCS, or for the whole database from SQL.

Clean the data

Rectify the errors related to data quality to reduce the code churn for validations and error processing.

Configurations to test during data migration test runs

The following set of configurations can impact performance of data migration and therefore it is recommended that these are tested with different values to find the optimal configuration for your data migration scenario.

Configure entity execution parameters

Data management > Framework parameters > Entity settings > Configure entity execution parameters

 

Import threshold record count:

This value represent the number of records that will be split and assigned to separate tasks.

Import task count:

This values is use to determine how many threads will be used for the data migration job for a specific entity. For example, if the Maximum batch threads = 8 for each server, and there are 4 servers assigned to the data migration batch group, the maximum value for Import task count should be 8 * 4 = 32 

  

Validations

Validation logic for record inserts or updates may have been incorporated into the system, and/or there may be validation on individual fields. If the validation logic is taking too long and if the data migration process is mature enough to confidently disable these options for cutover this should be considered.

Data management > Data entities > Entity structure

 

 Run business validations:

If this flag is enabled, the system will execute any logic written into the validateWrite() method on the table, and related event handlers.

Run business logic in insert or update method:

If this flag is enabled, the system will execute any logic written into the insert() or update() method on the table, and related event handlers.

Data management > Data entities > Modify target mapping

 

Call validate Field method:

If this option is enabled the validateField(FieldId p1) method will be called for the specific field.

  

Data migration performance optimization process

• It is recommended to break up large files into smaller chunks. The reason for this is that it allows time for the SQL optimizer to determine if a new query plan will be optimal.
• Performance should be tested in an appropriate tier 2+ environment.
• Performance should be tested in a mock cutover prior to Go-Live. 

Data migration performance testing is an iterative process, thus it is suggested that information regarding each test is collected and compared to determine the optimal configuration for a specific entity, for example: 

Server Configuration:

Batch group used: <Name of batch group>

Number of AOSs assigned to batch group: <Number of AOSs in batch group>

Maximum batch threads: <Number of batch threads assigned to each AOS>

Entity	Number Records	Source Format	Change Tracking Disabled	Set-based Processing	Import Threshold Record Count	Import Task Count	Run Business Validation	Run Business Logic In Insert Or Update Method	Call Validate Field Method	Required Performance	Actual Performance
<Name of entity being tested>	<Number of records being imported>	<Source format of data to be imported>	<Yes/No>	<Yes/No>	<Number of records>	<Number of tasks>	<Yes/No>	<Yes/No>	<Yes/No> <Potential field list>	<Time required for import to complete in order to achieve cutover window>	<Actual time taken to import records>

There are additional areas where performance can be optimized, for example analysing the specific queries and query plans, however those processes are covered in other articles and are not intended for this blog.

If you would like to read additional steps for performance troubleshooting and optimization the following articles are a good starting point: