The 7 milestones of a data migration

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

Open

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

Open

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:

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.

4.2 Contextualization of D365: Two kinds of pressure

Context of Dynamics CRM 2011 and Dynamics 365

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

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.

5.2 Digging into strategies and actions

Implementing actions plan with specific tools

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.

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.

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).

Milestone 1 : Creating Basic Requirements

Milestone 2 : Preparing data related to the CRM Organization (database)

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

Step 2 : Grouping and prioritizing CRM security data

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.

• 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]

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

Step 4 : Adjusting Migration Code

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

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.

Step 3 : Generating data related to CRM entities.

Milestone 6 : Validating migrated data

Step 1 : CRM Deployment [11]

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

Step 4 : Adjusting Migration Code

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

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

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).