The 5 pitfalls of a data migration - making a data migration painful

Summary

This white paper provides an overview and a detailed view of pitfalls making painful a data migration related to Dynamics 365 or Dynamics CRM.

Regardless the type of data migration (migration to Dynamics online or migration to Dynamics On-Premises), native and custom items could delay the data migration process.

Those items should be categorized into 5 groups: data context, platform context, functional context, code context, architecture context.

Keywords

Data Migration, D365, Dynamics 365 On-Premise, Dynamics CRM On-Premise, Pitfalls, Data Context, Platform, Code, Customizations, CRM services, Data security Model, CRM Functionalities, CRM Database.

Confidentiality

The white paper does not provide any confidential client data related to data migration. All the code in this white paper is only related to the CRM SDK365 code.

Introduction: What are the main pitfalls during migration?

A data migration focuses on the movement of data between source (legacy data system and business) and destination (target system).  Hence, in our white paper, a data migration will focus on the movement of data between Dynamics CRM / 365 on-premises (source) and Dynamics CRM / 365 on-premise or online (destination) [1].

However, pitfalls are real and can delay the data migration. Even with the most thoroughly tested tools and procedures, we need to identify those pitfalls and find a way to overcome the latter.

Data migration options

Without emphasizing the technical aspect of an option, in this case, whether it is a "Cloud" technology (cloud technology) or an "on Premise" technology, there are two execution contexts [2]:

Data migration options applied to Dynamics CRM

Emphasizing the technical aspect of an option, what does it mean regarding the platform Dynamics CRM or Dynamics 365?

From these options, could be extracted the cases of so-called figures specific to each execution context mentioned in the table above (types a, b, c, d, and e). So, what is a specific option?

Data migration options in details

A specific scenario is characterized by the following three elements: the data and metadata structure, the technology or platform in which the transfer is constrained and the technological context, and finally, our last element, which is the version and build number of the platform.

Description of option “type A”: from database to database, from Dynamics on-prem to Dynamics on-prem

Description of option “type B”: from database to service, from Dynamics on-prem to Dynamics online

Selected option

Before the list of types mentioned above, it should be noted that our data migration takes place in TYPE A and more precisely in TYPE a-3.

Migration is the migration of data considering the three elements of our definition regarding our option:

1. Generic Framework

Based on a generic framework, we asked ourselves questions related to the following concept: pitfalls …what are the pitfalls we could face during a data migration? To answer this question, I decided to regroup them in 5 categories: data context, platform context, functional context, code context and finally, architecture context.

Open

2. Pitfalls Overview

During the data migration process and precisely during the milestones of data migration, pitfalls should occur. Each pitfall mentioned above (data context, platform context, functional context, code context and architecture context) should occur in all milestones of your data migration and should delay the process. A data migration is composed of important milestones in transferring business and system data from one Dynamics CRM platform to a new Dynamics 365 platform. Hence, a milestone is not a key factor in Dynamics CRM data migration.

Open

3. Pitfalls Details

3.1 Pitfalls from data context

Data context could be categorized into five categories: data security model, data sharing, data model denormalization, data logs and audit and finally, data volume.

Data CRM Security Model

• Complexity of data security model in the source:

Data Sharing

• What can be shared? Is there a limit to migrate shared data?

Data Model Denormalization

Dynamics CRM allows to create a lot of relationships between entities and so, between tables. To do so, we can create as much as possible, those relationships in several ways:

• Create objects such as lookup and customer in our business entities (native & custom).

• Assign a record to a user or a team can increase the denormalization of the data because we update the ownership of the record. Ownership (OwnerId, Owner Team, Owner Business Unit, Owner User) concept is linked to all entities even the custom we create.

• Also, “Regarding Object Id” can be linked to entities and increase the denormalization of our data model.

Data Logs and Audit

Different types of logs should be considered as pitfalls because the data could be relevant. But do we need to keep logs related to internal CRM process or custom process we developed?

• Logs regarding the management of mailboxes in CRM by the server-side-sync could be relevant (i.e. Server-Side-Sync stores logs into “Trace log” table).

• Logs regarding the customization deployed in CRM organization could be relevant (i.e. execution of workflows are stored in “Workflow Log” table).

• Logs created by internal CRM process (i.e. CRM Async Service uses data from AsyncOperation table to execute internal process, CRM Async Service storing data in “Bulk Operation Log” table).

• Logs created to audit the user actions in Dynamics CRM could be relevant for business (i.e. CRM SDK365 logs relevant data related to the following operations: update, create, delete, etc.).

• Dynamics 365 App for Outlook (Server-Side Sync) uses 2 tracing mechanisms: Alert Wall (“TraceLog” table of CRM database) and Telemetry :

Data Volume (Row Number and Denormalization)

Data volume could be a pitfall because:

• Bigger is the size of the database, longer should be the data migration.

• Bigger is the size of the database, more functional testing and CRM deployment should be necessary.

• Bigger is the size of the database, more indexes management should be necessary. Moreover, indexes management will be impacted by the degree of database denormalization.

• Bigger is the size of the database, bigger should be the impact on the performance: 2 examples should be mentioned:

3.2 Pitfalls from platform context

Platform Cumulative Updates and Hotfixes (Version 8.2 & 9.0)

Regarding each major version of Dynamics CRM/365 on-premise (i.e. version 8.2 or version 9.0), there are a lot of cumulative updates: each month, until December 2020, there is a new update rollup include all the hotfixes that were released for limited distribution. The rollups have 2 purposes: enhance unexpected behavior and repair functionality.

Here is the list of cumulative updates of December 2020 for both major version of Dynamics (8.2 & 9.0) :

• Dynamics 9.0: 21 rollups are available – from March 2019 to December 2020.

• Dynamics 8.2: 25 rollups are available – from October 2019 to December 2020.

Update the version of the platform could be a pitfall because:

• More we update the platform; more we need to include a new rollup deployment in the data migration;

Black Box Behavior (Front-End and Back-End CRM Services)

Dynamics CRM/365 on-premise is a black box and so, unexpected behavior could derive from two services: CRM frontend services (CRM Client-code) and CRM backend services (CRM Async Service & CRM Async Service Maintenance). All the behaviors and the communication between the frontend and backend services are not easily apprehended, although we can:

• decompile CRM backend services code and we can extract and analyze client-code (JavaScript);

• extract and analyze SQL queries (i.e. stored procedures) of CRM.

System and Custom Metadata (Mapping old to new D365 values)

All retrievable, system and custom metadata has a “MetadataId” primary key to make it unique and has values that can be retrieved by primary key or by name.

The values extracted from the database source could be different from the database or platform destination. Then, we should map the old values (source values) to the new values (destination values).

3.3 Pitfalls from functional context

Non-Supported Functionalities (system functionalities)

Regarding Dynamics CRM, there are two groups of functionalities : one group related to inner CRM workings (i.e. auto-numbering management, relationship roles management, duplicate detection rules management, and many more) and another group related to integration CRM workings (i.e. SharePoint integration and document management, Exchange integration and mailbox management, and many more). If custom functionalities are replacing native functionalities, they could become pitfalls in a data migration process because:

• migration code must adapt to new data and metadata model (dynamics on-premise, on-line);

• or replace custom functionalities with native functionalities and then, adapt migrated data to the new data model.

Overridden Functionalities (unsupported JavaScript)

Web Resources in Dynamics CRM could be the place to override native CRM functionalities. Exactly, it is possible to override native functionalities with JavaScript (client-code), functionalities related to:

• CRM forms: via “crmForm” tag, we can override native functionalities (i.e. resize the form).

• CRM Ribbons: via custom utilities, we can override native functionalities related to CRM component (i.e. CRM grid).

• CRM attributes: via custom utilities, we can override native functionalities related to native or custom attributes (i.e. turbo forms that are a new form rendering option in Dynamics 365 and not legacy form rendering).

• CRM pages: via custom utilities, we can override the page refreshing from a save event.

• CRM navigations: via custom utilities, we can override the native JavaScript event (i.e. onclick event).

3.4 Pitfalls from code and customization context

Customization Dependencies

In dynamics CRM, there is a way to determine the level of customization and so, the level of customization dependencies: DependencyBase and DependencyNodeBase tables are the starting point of our analysis and help to establish 2 points:

Regarding the mentioned points above, dependencies are related to the component type : entity, entity relationship, attribute, site map, saved query, save query visualization, ribbon command, ribbon context group, ribbon customization, ribbon rule, ribbon tab, ribbon diff, system form, etc.

Code and Customization Cohabitation and Dependencies (C# and JavaScript)

In Dynamics CRM, there are mainly 2 forms of development: no-code development (customization with workflows) and code development (plug-ins and code activity in C#, client-code in JavaScript). However, when real-time workflows are combined with plug-ins, 2 problems can occur:

If we have a design consideration when mixing plug-ins and workflows in Dynamics CRM, then how could we integrate this consideration into the data migration process?

3.5 Pitfalls from Architecture context

Embedded CRM Systems (Dynamics 365 App for Outlook)

Dynamics CRM offers the opportunity to use an embedded application: “Dynamics 365 App for Outlook”. The latter helps the users to track CRM entities (CRM data) from MS Outlook (Office 365, Microsoft 365). Dynamics 365 App for Outlook integration could be a pitfall for the following reasons :

• If users have tracked data into CRM by accident and it was never cleaned on the other side, then, we need to include a strategy considering this severity of the issue: data is confidential. It will automatically impact the execution of the migration code and delay our migration process.

•  Dynamics 365 App for outlook uses “Server-Side Sync” mechanism to enable the user mailbox:

CRM System Maintenance (Log and AsyncOperation table)

Because “AsyncOperation” table is become too large, it could affect the performance and delay the data migration. The database is become bigger because asynchronous background operations (i.e. asynchronous workflows, bulk operations, system events, updates match codes…) save data in “AsyncOperation” table (i.e. which entity and with entry (id) is processed, at which time the operation finished, who is the owner of the task and many more).

Data migration process needs to determine the number of records to delete. Precisely, process needs to target the right data and perform the right cleanup of the table. To do so, process must include the list of the following tables:

However, process needs to be very accurate because all data in “PrincipalObjectAccess” and “WorkflowLogBase” tables should not necessarily be deleted and should be kept because of business needs and business audits.

CRM System Externalization

Queue-Based System

Custom CRM functionalities developed for custom business needs need to be executed in batch mode. But, they can be and should be externalized because it will help to:

Architecture queue-based could help to externalize custom CRM functionalities. However, data migration process should include this queueing process because data could be in a waiting line and could be a pitfall. Regarding queue-based architecture, many patterns exist (Publisher/Subscriber, Priority Queue, Queue-Based Load Leveling, etc.), question is the following:

How do we include data stocked in a waiting line in our data migration process? Regarding the pattern used to store data in a queue, we might not include data in a waiting line, but at what cost?

Notification-Based System

Dynamics CRM/365 Architecture allows 2 ways of notifying external applications about events in CRM by making requests to receiver endpoints with information about the events:

Architecture notification-based could help to notify external applications. However, data between applications may be out of sync. And this could be a pitfall. Regarding notification-based architecture, question is the following: How do we include data out of sync in our data migration process? Regarding the pattern used to notify external applications, we might not include data in our process, but at what cost?

CRM Solutions Deployment (CRM Application Lifecycle Management)

Because we need to validate data which has been migrated, we need to deploy our CRM solutions (zip files) to evaluate the impact of the new data during and after the CRM deployment process. However, different ways of organizing CRM solutions in a CRM organization could be a pitfall because CRM solutions create dependencies into the database:

Conclusion