The 7 milestones of a data migration

For all references and definitions, see the page called “References & Glossary for the 7 milestones of…”.

Summary

The data migration from Dynamics CRM 2011 (on premise) to Microsoft Dynamics 365 (on premise, Version 8.2) means to migrate the business data, system data and metadata from CRM 2011 to D365. This process requires a methodology adapted to the D365 platform because it is carried out in a "big bang" approach considering the technological context and technical constraints that continually put pressure on the CRM data and metadata transfer process.

 The methodology is characterized by a process in terms of strategy and dynamics (stages), which is supported by .NET tools, Dynamics 365, and SQL Server technologies. So, these tools have helped us with the implementation of our strategies: organizing our data model, grouping, and ordering our entities, excluding our entities from the process, and adjusting our migration code. In addition, these tools help us in every step of the process that we had previously established: creating the essential requirements, preparing the basic data, transferring the data, and finalizing the data.

Keywords

Data Migration, big bang approach, methodology, metadata, D365, Dynamics 365 On-Premise, Dynamics CRM On-Premise, Microsoft SQL server 2017, SSIS, SQL Server Integration Services, .NET Dynamic Link Library (DLL), Jet Brains Decompiler.

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 (downloadable for free on the Web) .

1. Introduction: What questions to ask before migrating ?

1.1 The selected data migration approach: big bang approach versus phased approach 

In general, a data migration is a mass movement of data from a source to a destination. The data movement can be performed according to following approaches: the "big bang" approach and the “phase " approach.

The "Big Bang" approach means that the migration takes place only once and we know when it starts and when it ends. This approach requires, in principle, a ”shut down” of the system including the data source to be migrated. On the other hand, the phased approach, although it complicates the migration process because the data migrated to the destination has been modified in the original source, allows smaller pieces of data (and metadata) to be migrated.

Whatever approach is chosen, data migration takes place in a specific scenario, a scenario determined by its technological environment. Before describing our scenario, let us first establish the existing and potential list of options related to data migration.

2. Data migration options

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

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?

2.1 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

Description of option “type B”: from database to service

Description of option “type C”: from « flat file » to database

Description of option “type D”: from « flat file » to « flat File »

Description of “type E”: from service to service

2.2 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:

  • The data and metadata structure of the 2011 CRM differs from the data and metadata structure of the D365.

  • The technology and the platform are different. From a .NET point of view, even if the platforms are based on the .NET Framework, their physical and application architectures differ from each other.

  • Therefore, their versions and build numbers differ from each other.

3. Generic Framework

Based on a generic framework, we asked ourselves questions related to the following concepts: From where, how, from what, which tools, what are the phases of migration, what …

3.1 Implementation assumptions

The fundamental problems we faced in a data migration process are:

Where does the process of migration come from? This question is not limited to the original data source, but also to the dynamic context in which this data source continues to evolve. The main features of this changing context are: 

The data volume and complexity of data integrity
The evolution of the data structure  
The evolution of the code deployed in the application system
The upgrade of the platform or technology
The evolution of dependencies between the components of the system.
The Hidden behaviour of a platform (black box) 

Where does the process of migration come from? This question also refers to the constraints which put pressure on the migration process, constraints which can play a determining role in the time allocated to migration: 

The physical infrastructure or resources 
The initial application architecture 
The Deployment management 
The Deployment Tools
The validation of intermediate results
The time and performance constraints  

What does the process migrate? Of course, data or even metadata, although these may be commonly referred to as ”entities". Roughly, how much data and metadata are being migrated? 

Which data and metadata from the source database should be migrated? 
Which data and metadata of the database need transformation in the process? 
Which data and metadata in the source database do not need to be migrated? 

How to execute the process of migration? So, which actions are required to structure and launch the data transfer to the destination?

How can we support these actions? 

How do we organize our actions and orchestrate the process?  

To structure the migration process over time. 
To finalize the new data source.
To control and validate the migration process itself? Also, validate intermediate and results?

4. Describing the context of D365 data migration

4.1 Starting point and goal of D365 data migration

The starting point of the D365 migration is a 2011 SQL Server CRM database with the SQL instance version 11.0 (MS SQL Server 2012). From this starting point, the objective is multiple :

  • To migrate approximatively 1.5 terabytes of data in a new database with different data and metadata structure, in a SQL Server database (SQL instance version is 14.0, MS SQL Server 2016).

  • Migrate knowing the fact: Asynchronous and synchronous CRM services do not behave in the same way as those of Dynamics CRM 2011.

  • The migration of nearly 1.5 terabytes of data, including approximatively one terabytes of data related to the e-mail and mail merge templates in the CRM, is a constraint in terms of performance and time.

  • To migrate within a short time interval of 10 to 15 hours of acceptable migration.

Data Migration Process

4.2 Contextualization of D365: Two kinds of pressure

Context of Dynamics CRM 2011 and Dynamics 365

CRM Context
Data Context
  1. Data volume context leading to a larger data integrity issue: 1.5 terabytes of data, data modified by external applications via an integration service (web services).

  2. Context of the metadata and data structure via the CRM configuration, context of the native metadata of CRM 2011 different from the D365 metadata context (version 8.2 and 8.3).

  3. Data normalization impacted by data denormalization : increase of the relationships (one-to-one relationships, one-to-may relationships, many-to-many relationships).

  4. Data integrity is a challenge because of the data volume.

Platform Context
  1. Background to the continuous update of the D365 platform: minor evolution of the "Build" numbers (Upgrade from version 8.2 to 8.3).

  2. Black box context: CRM services deployed in full-server architecture mode could have unpredictable behaviors and CRM Services code could not be understood in its entirety.

Functional Context
  1. Background of the evolution of the native and custom functionalities : impact of the upgrade and hot fixes (minor and major evolution).

  2. Use of non-supported functionalities.

Code Context
  1. Dependency context or dependency nodes between native code and native SQL requests D365: dependency between JavaScript (CRM forms code), C# (Plugins custom code), .NET Framework and finally, native SQL requests of CRM database.

  2. Background of the evolution of the code responding to business needs (code-client JS and code-Server Plugin). 

  3. Deployment of custom code and non-supported code.

Technical constraints regarding D365

Technical constraints
D365 Native Architecture constraints
  • Change of the security model with “Mailbox "concept, change of the logging model by the addition of new “log" tables, change of the basic metadata 1-either of their object: entity, attribute, relation, set option ... - or of their categorisation (”entitytypecode").

  • Behaviour constraints of synchronous CRM (IIS) and asynchronous CRM (Async CRM) services: increasing interaction in contrast to CRM 2011. Asynchronous behavior is much more present in D365-CRM Async Services behave according to the data entered in certain tables   (AsyncOperation table,...) ... also, constraints related to SQL queries native to the platform (accessible), queries that are called by CRM processes from the client interface or from backend CRM services.

Infrastructure constraints
  • Physical resources available to validate the migration process: disk space, processor, transfer of backups, …

Deployment Constraints
  • CRM deployment process from each database backup (groups / zones). The Dynamics 365 Deployment Manager tool allows to create a D365 CRM organization while using the user mapping process, an internal process that will implicitly validate part of the D365 security model (user, business unit and team) and validates the user synchronization process with the "Active Directory" (server-type authentication).

Development Tools
  • Constraints related to the optimization of the SQL code due to the time interval allowed (12-15 h) for total data migration.

  • Integrated Development Environment (IDE) used for the development of the code (SSIS) and the deployment of the code generated from the IDE in the database server.

 

Once the context and the technical constraints have been established, it is time to present the processes of data migration organization : grouping and ordering of entities. These processes or strategies were created using tools specific to the “Dynamics 365 " technology and the “.NET” technology, tools that enabled us to extract relevant and detailed information.

In other words, before approaching the data migration process as such, it is necessary to present the organisational process and its sub-processes as well as the tools that have enabled the organisational processes to reach the desired level of maturity or required for data migration.

5. Applying strategies and actions in the 7 milestones

5.1 Describing strategies and actions

I intend to focus on the strategy as process and the actions helping the process move forward.

Generic Framework for strategy and actions

5.2 Digging into strategies and actions

Digging into strategies and actions

Implementing actions plan with specific tools

STRATEGIES

ACTION

TOOLS

STRATEGIES

ACTION

TOOLS

GO (GROUPING/ORDERING)

 

Extracting : Metadata, Relationships, Data Model

D365 Metadata Diagram Tool & Custom Console Apps.

Extracting : Native and Custom Data Model

SQL Management Studio Object Explorer (DB Diagram functionality)

Extracting : D365 Native Code SQL

SQL Management Studio (Views & Stored Procedures)

Decompiling D365 .NET Code (C#) : Object Model (Business Entities, Relationships), Internal Services, etc.

DotPeek Decompiler

Validating : CRM Security Model, CRM Deployment Service.

D365 Deployment Manager

Validating : Execution of functionalities (CRM Security Model)

MS SQL Profiler

Validating : CRM Security Model, native functionalities

D365 Platform

GOE (GROUPING/ORDERING/EXCLUDING)

Retargeting : CRM Security Model, Data Model, Relationships

N/A

Cleaning CRM Data

D365 SDK

Adjusting : data migration custom code

N/A

Defining strategy through actions : from grouping to prioritizing strategy

It is about creating groups that contain each of the subgroups we have given the name “ZONE". Concretely, in Group 1, there could be several "ZONES" or zone 1, zone 2 ... Each of the zones that may include entities.

 In each zone, we can find as many entities as possible of type “SYSTEM “(native to the D365 platform) and entities of type ”CUSTOM" (which have been added to the platform to meet business needs). For example, in Group 1 called "Group 1-BASE", we could have 3 zones or the following zones with a variable number of entities : zone 1 including the entities ”Currency” and “Owner”, zone 2 including “User”, “Business Unit”, “Relationship", etc.. zone 3 includes “Website”, “Goal Metric”, “Territory”, etc.

So, this involves ordering groups and associated zones. Of course, quantifying the group and the areas established the order of the migration process.

The two processes were constructed and finalized by using tools to generate specific processes or actions or generating processes.

Throughout these processes of grouping and ordering, a process of entity exclusion was built at the same time. Certain entities should not be considered in the migration process.

Defining strategy through actions : from grouping and prioritizing to excluding strategy

As result, it has become possible to establish two groups of entities: those that are an integral part of the data migration and those that are excluded from the same migration process.

There are many reasons why some entities and therefore some data migration has been set aside. The following is a list of the reasons that led to the exclusion of the said entities or tables:

  • Entities or tables that are "populated“ by importing our additional configuration (forms, attributes, views, security roles, etc.) and custom code (client code like JavaScript,  CRM solution files (”zip")).

  • Entities or tables that are "populated" by the system in the background (by the synchronous and asynchronous services of the CRM): some are used as logging and others as the source of operation for the CRM services. 

  • Entities or tables that are not ”populated" because they were not in the original system (CRM 2011) and therefore, no need to migrate data in the D365 version.

  • Entities or tables that are not "populated" because they are new in D365 and did not exist in 2011 version.

The table below presents the list of tables (entities), organized according to the four reasons mentioned above. However, the list is not exhaustive for a clear reason.

EXCLUSION REASONS 

ENTITIES EXCLUDED FROM MIGRATION

CUSTOMIZATION AND CUSTOM CODE 

TABLES RELATED TO METADATA… 

CRM SYSTEM/INTERNAL PROCESS

TABLES RELATED TO SYSTEM/INTERNAL PROCESS (ASYNCOPERATION,…)

CRM 2011 DATABASE SOURCE WE DON’T WANT OR DON’T NEED ANYMORE

TABLES RELATED TO QUOTE… 

NEW ENTITIES IN NEW D365 (VERSION 8 & 9)

TABLES RELATED TO MAILBOX…. 

5.3 How to process data migration through 7 milestones

Presenting a generic framework for 7 milestones

I intend to focus on the dynamics as process and on the milestones as a part of the process.

Generic Framework

Digging into the milestones

There are 7 milestones to consider : creating necessary requirements (number 1), preparing data related to CRM entities and metadata (number 2), transferring CRM security data (number 3), validating CRM security data (number 4), transferring CRM data (number 5), validating CRM data (number 6) and completing CRM data (number 7).

The 7 milestones
Milestone 1 : Creating Basic Requirements

PHASE

INNER STEPS

PHASE

INNER STEPS

Preparing database as template

Step 1: D365 “Deployment Manager” will help to create a CRM database “CRM_MSCRM” (CRM SQL Instance without custom configuration and code). Hence, it will create a CRM Organization (application side).

Step 2: Importing the custom “CRM solutions”. Hence, it will complete the native data and metadata model of CRM that will later be associated with our data.

Step 3: Validating the relevant foreign keys following the previous step 2.

Step 4: Creating a database backup will create our basic "Template" for future identical migrations.

Step 5: D365 “Deployment Manager” will help to delete the CRM Organization created at step 1.

Preparing database as tool and as development support

Step 1: Creating a database tool as a toolbox useful as much for the source database (CRM 2011) as for the destination database (D365).

Step 2 : Creating the first layer of abstraction in our toolbox including SQL objects as synonyms [2]. Hence, it will support the process of migration.

Step 3 : Completing the first layer creating a second layer of abstraction i.e. additional objects as stocked procedures and functions [3]. Hence, it will as well, support the process of migration.

Step 4 : Creating a database tool backup for future identical migrations.

Extracting SQL native code

Step 1 : D365 Metadata Diagram Tool [4] will help to extract the custom entities model, including the native entities. Hence, the tool will dig into details :

  • logical relationships because the database could be highly denormalized : one-to-one relationships, one-to-many relationships, many-to-may relationships.

  • level of ownership : business-owned, organization-owned, user-owned or not owned.

Step 2 : Creating a file backup will be helpful for future migrations.

Step 3 : Extracting the CRM model from the “Object Explorer” [5] in the SQL instance will create an additional tool to visualize the relationships. However, it will provide only a high-level view.

Extracting CRM entities

Step 1 : DotPeek Tool[6] will help to decompile the CRM native code (.Net library accessible in a specific directory in the server)[7]. Hence, the tool will dig into details :

entity and business layer (i.e. business entity); entity and data access layer (i.e. entity internal service); entity and configuration layer (i.e. object model configuration); client layer (i.e. authentication for Active Directory); entity and metadata layer (i.e. managed properties).

 

Step 1 : Cleaning data (security, system and business data)

Step 2 : Grouping and prioritizing CRM security data

Milestone is mainly composed of two strategies mentioned above and will be impacted by step 1: Implementing actions plan with specific tools and Defining strategy through actions: from grouping and prioritizing to excluding strategy

 

The strategies focus only on CRM security entities : System User, Team and Business Unit.

Moreover, they focus on all the entities related to CRM security model : logical relationships (i.e. relationship with “ownership”) and physical (i.e. relationships with tables foreign key)[8].

 

 

 

 

 

 

 

Milestone 3 : Transferring CRM security data to D365

Step 1 : Transferring entities related to D365 security model and D365 metadata.

First, the process involves preparing the main entities associated with the D365 metadata: "Solution" entity / "SolutionBase" table (name in database “CRM_MSCRM”), "Solution Component" entity / "SolutionComponentBase" table (name in database “CRM_MSCRM”), "Localized Label" entity / "LocalizedLabelBase" table (name in database “CRM_MSCRM”).

In a way, it is a question of updating identifying him unique (Guid or Globally Unique Identifier) of entity "solution" in mentioned entities (solution, solution component localised and quality-label) to replace that of solution D365 (initially created in "Template" of base) with that of solution CRM 2011.

Also, the process consists in preparing the main entities related to the D365 model of security: 

  • Entity "Team“ or table "TeamBase“ (table name in database “CRM_MSCRM”)

  • Entity "User" or table "SystemUserBase“ (table name in database “CRM_MSCRM”)

  • Entity “Business Unit” or table "BusinessUnitBase“ (table name in database “CRM_MSCRM”)

  • Entity "Queue" or table "QueueBase“ (table name in database “CRM_MSCRM”)

  • Entity "Mailbox“ or table "MailboxBase“ (table name in database “CRM_MSCRM”)

Step 2 : Logging data related to CRM security entities

During the data transfer, once the updated entity, could be logged, it is possible to show the result of changed data. Once the logging ended for every table, an electronic mail is sent to the desired addressees.

Step 3 : Generating data related to CRM security entities

Once CRM security data have been logged, generating a report of results in a « Excel » file. The latter could introduce a more deepened explanation regarding the logged data. 

 

Milestone 4 : Validating Data

Step 1 : CRM Deployment [9]

Complete validation limits itself not only to a report of data containing logical explanation. It must go farther as much as SDK of CRM is capable of validating results got in the table of logging.

D365 platform contains in a way a « SDK Deployment ». The latter helps us to import a CRM organisation from the database.  During the process, it is mandatory to undertake a mapping of users. Hence, it helps to synchronise the user with « Active Directory » and finally, it helps us to validate a part of CRM security Model.

Step 2 : CRM Security Data Validation

  • Visualizing logged CRM security data (previously, step 2 – Milestone 3)

  • Visualizing CRM security data reporting (previously, step 3 – Milestone 3)

  • Functional testing and CRM security entities : Once the ended importation, it is necessary to go further and so, to call CRM services CRM and test the data related to CRM security Model. Hence, we execute CRUD operations (Create, Read, Update and Delete) and we try to disable data related to the user [10].

Step 3 : Retargeting, Grouping and Prioritizing

Retarget the entities will help to regroup and prioritize the CRM security entities. Retargeting means that we must focus as much as possible on the logical and physical relationships.

Step 4 : Adjusting Migration Code

If we need to retarget the security entities and their relationships, we need to adjust our migration code.

Step 5 : Backing up the database (CRM security Model)

If we do not need to apply steps 3 and 4, we could right away back up the database and access to the next milestone.

 

Milestone 5 : Completing CRM data transfer (business and system)

Milestone 4 is a requirement for the milestone because we cannot migrate all the CRM data if CRM security data migration is not a success.

Step 1 : Transferring entities related to D365 business and system data. This step should be impacted by the step 1 of milestone 2 (preparing data).

Step 2 : Logging data related to CRM entities.

During the data transfer, once the updated entity, could be logged, it is possible to show the result of changed data. Once the logging ended for every table, an electronic mail is sent to the desired addressees.

Step 3 : Generating data related to CRM entities.

Once CRM data have been logged, generating a report of results in a « Excel » file. The latter could introduce a more deepened explanation regarding the logged data. 

Milestone 6 : Validating migrated data

Step 1 : CRM Deployment [11]

Complete validation limits itself not only to a report of data containing logical explanation. It must go farther as much as SDK of CRM is capable of validating results got in the table of logging. Before validating the migration, we need to import a CRM organisation from the database (“SDK Deployment”).  

Step 2 : CRM business and system Data Validation

  • Visualizing logged CRM data (previously, step 2 – Milestone 5)

  • Visualizing CRM data reporting (previously, step 3 – Milestone 5)

  • Functional testing and CRM entities : Once the ended importation, it is necessary to go further and so, to call CRM services CRM and test the data related to CRM Model. Hence, we execute CRUD operations (Create, Read, Update and Delete) and we try to disable data [12].

Step 3 : Retargeting, Grouping and Prioritizing

Retarget the entities will help to regroup and prioritize the CRM entities. Retargeting means that we must focus as much as possible on the logical and physical relationships.

Step 4 : Adjusting Migration Code

If we need to retarget the entities and their relationships, we need to adjust our migration code.

Step 5 : Backing up the database (CRM business and system Model)

If we do not need to apply steps 3 and 4, we could right away back up the database and access to the next milestone.

Milestone 7 : Finalization of data

Step 1 : CRM Deployment : two subprocesses

  • Deploy the requirements related to the database : manage the indexes and the symmetrical keys at the security level.

  • Import the CRM Organization from the database (“SDK Deployment”).

  • Import the CRM configuration (business and system).

  • Import the CRM custom code.

 

Step 2 : Primary validation - CRM Security Data Validation (apply step 2 and 3, milestone 3)

Step 3 : CRM Data Validation (apply step 2 and 3, milestone 6)

 

Iterating between the milestones

Iteration between milestones

The first iteration occurs between the milestones 3 and 4 : after validating CRM security data (User, Business unit, Team) and data related to CRM security data (milestone 4),  a failed validation takes a step backward (milestone 3).

Once the milestone 4 is a success, the second iteration occurs between the milestones 5 and 6 : after validating CRM data (business and system data), a failed validation takes a step backward (milestone 5).