When moving data from either a homegrown application or even a pre-existing Dynamics365 system there are few tricks that can save you a great deal of time and take your project beyond simply completing the data migration and to instead making it a cornerstone in the success of your migration to Dynamics365. I’ve done a number of data migrations ranging from the small (less than 25,000 records from one database) to the larger (greater than 1,000,000+ records from a variety of databases).
Whether you are using tools like Scribe or Kingsway or building your own application – you should always use the available SDKs and not go to the database. In writing that I feel as though as this is slowly becoming a double-negative as you cannot access the database directly for cloud instances of Dynamics365.
Check your Indexes
If you are migrating from/to an on-premise system and doing a substantial amount of writebacks (i.e., migrated on) to the source system reset your indexes BEFORE starting the migration to optimize your SELECT statements. If you are migrating a million+ records, you might want to chunk out the datasets and refresh your indexes in between to clean up any changes.
The hardest part in any data migration is lining up the Ids in one system to the new Ids in Dynamics365. Hint: They won’t match. One tactic that has saved me multiple times is adding fields specific for the migration that hold corresponding Ids from the source system (and make the read/write). Firstly, it lets you see what data is linked to what in the new system and secondly if you are having to do some synchronizations later and the link is bad, all you need to do is change the field (and not re-run all your code again). I’ve only run across a few instances where users really objected to having the extra fields in their implementation, so we put them into their own section that users could collapse/expand. In subsequent releases, once all your work is done you can remove the fields permanently.
Guided Process Stages
How great is it to implement a new Guided Process Flow with 5 stages only to migrate a ton of closed records but still have them show in the initial stage? It looks pretty bad. I’m currently working on this challenge on an active project right now and will be writing a separate post on this. But this is one of those small things that really helps the users when looking at migrated data and seeing how it fits into their system and takes your project from good to great.
Migrate Lookup Data First
Before migrating any user data, isolate the lookup/domain data. Of either type of data, lookup data is the most ripe for change – whether it is converting from different field types, option sets or creating new record associations, the lookup data can involve the most transformations you do. Migrating your lookup data first let’s you prove out many of your assumptions on the rest of the user data while solving complex association problems that could popup when user data needs to access those records for lookups in Dynamics365.
Don’t forget about the users
Mapping users from one system to another is not always straightforward and right behind migrating lookup data should be the next item that you raise up. No one wants to see all of their records or ownership show as “Data Migration User”. If you cannot map them based on NT account, add a new field to the systemuser entity that provides the mapping attribute and update it before you get to the migration stage.
If you have this information in your source system you are going to want to send it over to your new system. After all, you didn’t sign up 100,000 new accounts all on the same day. Dynamics365 has a hidden field called overridencreatedon that when set, tells Dynamics to show this field instead of the default CreatedOn (which incidentally will store the data of migration when the record was actually created). Accessing this field is supported via the SDK.
Depending on how you have structured your migration and how many data sources you are migrating from, long running transactions, even SELECTS can become a problem. If your source data is not changing (i.e., you have kicked all your users out), you can gain some performance improvements by changing your SQL Isolation level to READ UNCOMMITTED – this will allow for faster reads but if users are in the system, you could run the risk of pulling in uncommitted data.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
There are other pieces of Dynamics data migrations that I’ll handle in later posts and arguably these are much more focussed on the architecture vs the actual implementation. The key point to remember is that the better you do at your migration, the more attention you pay to the small details, the more successful your overall migration and adoption of Dynamics365 will be.