Over the last few weeks, I have been involved in a huge data migration that has to run in a weekend. Below are the tips we discovered to achieve the best performance. Most of these tips are not specific to Dynamics CRM data migrations and can be reused with other technologies.
Just a note. Loading data through the Dynamics CRM API is painfully slow, perhaps this is something Microsoft can look into. I suspect the API speed is slow because of the record level security checks that occur on every transactions, I would appreciate if any readers can confirm or reject this!
IMPORTANT NOTE - when using the multi threading make sure you are not exceeding the 60,000 API per organisation within a 5 minute sliding schedule. The official details can be found at the following Microsoft Docs link. https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/api-limits.
1. Turn off Plugins and Workflows
If the Dynamics CRM solution contains a high number of plugins and workflows then turning them off will have a big performance impact. Specially if the they are real time plugins and workflows.
The disadvantage of this is the workflow and plugin logic has to be recreated in the "Pre Processing" of the data to be migrated. This means creating, maintaining and testing the same logic in two different places.
2. Use Batching
Instead of sending one request to the server at a time, send many requests to the server in one go. This reduces the number of calls to the server and therefore reduces the total latency time on the overall data migration.
Batching is usually achieved by using the ExecuteMultiple SDK message or if using SSIS and the Kingsway Soft connectors then the batch size can be configured. Experimentation will need to be done to find the optimal batch size.
3. Use Multi threading
Multi threading combined with batching will really help to improve data migration performance.
In simple terms multi threading allow the same piece of code to be execute many times at in the same process at the same time.
When using Kingsway Soft connectors there is a multi thread setting. I am wondering if this is new because I have not used it in my previous SSIS data migrations. Alternatively the Balanced Data Distributor Transformation can be used. If creating a .NET application then multi threading can be achieved using parallel programming, for example of parallel programming look at Microsoft Docs.
Be careful when multi threading. I often get over excited and try the maximun number of threads possible resulting in Out of Memory exceptions or hitting the API limits discussed at the beginning of this blog post.
4. Query Locally (and in memory) instead of querying the server.
Querying Dynamics CRM for data is a sure way to kill your data migration process. Think about it! When querying Dynamics CRM the request has to travel across the network/internet to the server/Microsoft Cloud, get the queried data and send it back to the client. This is a time consuming sequence of events, it is even more time consuming when repeated millions of times.
The best way to query Dynamics CRM data is to query a local database with copy of the Dynamics CRM data. The round trip time querying to query a local database is much quicker than querying the Dynamics CRM server. Additionally querying a database is generally much faster that query Dynamics CRM through an API.
A local copy of Dynamics CRM data can be sourced by using the Data Export Service, an SISS package that "downloads" the required records into database tables or a .NET component that downloads the Dynamics CRM data into database tables.
It is a good idea to obtain the required data in "pre processing" instead of during the data migration itself.
5. Pre Processing
Often the data to be pushed to CRM can be transformed to look like its destination ahead of time (before the migration). This means alot of the transformation work can be done "upfront" before the data migration is due to be executed.
6. Improve Hardware/Resources
This is an obvious one, simple but not always the best solution.. If you migration has a lot of transformations to perform then perhaps purchasing extra processing power and memory will speed up your migration.
7. Disable Auditing and Duplicate Detection Rules
It is often suggested by Microsoft to turn of auditing and duplicate detection rules. I have not done this before so I can not provide any insights into the performance impact.
Comments