Versions Compared

Old Version 9

changes.mady.by.user thierry sinassamy

Saved on

compared with

New Version 10

changes.mady.by.user thierry sinassamy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

…

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.

...

…

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-Premises, Dynamics CRM On-Premises, Orchestration, Guide, Ordering, Prioritization, Grouping, Reporting, Logging, Tables, Attributes, Columns, Entities, Data Model, Customer Data Model, Activity Data Model, Custom Entities, System Entities, Core Data Model, Security Data Model, Internal System Process, Sequencies, Validation.

 

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: MIGRATION CONTEXT

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:

...

Even with the most thoroughly tested tools and procedures, we need to ask ourselves how to orchestrate a data migration, mainly from database to database.

1. PROBLEM: HOW TO ORCHESTRATE THE DATA MODEL

Here is the opportunity to orchestrate a data migration code related to Dynamics 365. So, we need to have a guide helping us to develop the right, accurate and structured code regarding the data model.

The questions raised by this problem could be challenging: How to group the entity model? What is the sequence of execution? How to prioritize the entities? How to exclude the unnecessary entities to migrate? Our guide could help to organize and structure the data migration and in so doing, we could establish the right requirements to fulfill.

2. REQUIREMENTS RELATED TO THE ORCHESTRATION

2.1 Requirements related to Database SQL as migration tool

First, we need to create at least 3 databases: a « source » database (Dynamics 365); a « target » database (Dynamics 365) and finally, a « tool » database we could call « CRM Tool »What is the sequence of execution? How to prioritize the entities? How to exclude the unnecessary entities to migrate? Our guide could help to organize and structure the data migration and in so doing, we could establish the right requirements to fulfill.

2. REQUIREMENTS RELATED TO THE ORCHESTRATION

2.1 Requirements related to Database SQL as migration tool

First, we need to create at least 3 databases: a « source » database (Dynamics 365); a « target » database (Dynamics 365) and finally, a « tool » database we could call « CRM Tool ».

Cfm card
layoutSimple
colorhsl(214, 100%, 85%)
idizxsvbhgoi
titleCRM Tool Database as Tool
body1-Create tables of reference in “CRM Tool” database: mapping, logging and reporting. 2-Create SQL objects (SQL Synonyms and functions): to map, ta extract, to validate, to count…data. 3-Create SQL scripts as stored procedures.

Cfm card
layoutSimple
colorhsl(214, 100%, 85%)
idizxsvbhgoi82pzx7n2wwc
titleCRM Tool Dynamics 365 - Database as Toolsource
body1- Create tables of reference in “CRM Tool” database: mapping, logging and reporting. 2-Create SQL objects (SQL Synonyms and functions): to map, ta extract, to validate, to count…data. 3-Create SQL scripts as stored proceduresImport the database source in the development environment. 2- Administrate the database as requested. 3- Backup the database source.

Cfm card
layoutSimple
colorhsl(214, 100%, 85%)
id82pzx7n2wwck6p4p6dcymn
titleDynamics 365 - Database as sourcebody1- Import the database source in the development environment. 2target
body1- Create the new database with the Deployment Manager 365 (Deployment SDK). 2- Handle the entity model customizing the native model of CRM. 3- Administrate the database as requested. 3- Backup the database source.

Cfm card
layoutSimple
colorhsl(214, 100%, 85%)
idk6p4p6dcymn
titleDynamics 365 - Database as target
body1- Create the new database with the Deployment Manager 365 (Deployment SDK). 2- Handle the entity model customizing the native model of CRM. 3- Administrate the database as requested. 4- Backup the database target.

2.2 Requirements related to organization of entities.

Here is the opportunity to define our strategy and actions related to grouping, prioritizing and excluding entities in the process.

...

3. DATA PREPARATION: BEFORE DATA ORCHESTRATION

Here is the first steps to fulfill before migrating our data and orchestrating our data model management.

3.1 START THE DATA PREPARATION

  1. Initialization of the logging table in the database “CRM Tool”

  2. Initialization of the backup repository: repository will contain all the backups

  3. Cleaning and initialization of the reporting statement in the database “CRM Tool”

  4. Deploy the new customization to update the native data model of the new CRM organization: it will add the custom entities to the system entities and potentially update the relationships.

3.2 VARIABLES INITIALIZATION

  1. Initialization of the following variables: identifier of the “Organization” entity, identifier of the “Solution” entity, identifier of the “Business Unit” entity, identifier of the “System User” entity.

  2. Complete the identifier “System User” with its properties and the “User Settings” entity.

3.3 PREPARING CRM SECURITY MODEL

  1. Update the following entities in the new database (target): System User, Business Unit, Team.

  2. Complete the update with the following entities: organization, solution if needed.

3.4 END THE DATA PREPARATION

  1. Handle the indexes of the “target” database, executing a SQL command to disable the indexes.

  2. Logging the preparation in the database “CRM tool”.

  3. Create the backup of the preparation: “PREPARATION BEFORE DATA ORCHESTRATION”.

4. CORE MODEL DATA ORCHESTRATION: WAVE 0

...

SEQUENCE

...

ACTIONS

...

SEQUENCE 1

...

4- Backup the database target.

2.2 Requirements related to organization of entities.

Here is the opportunity to define our strategy and actions related to grouping, prioritizing and excluding entities in the process.

...

3. DATA PREPARATION: BEFORE DATA ORCHESTRATION

Here is the first steps to fulfill before migrating our data and orchestrating our data model management.

3.1 START THE DATA PREPARATION

  1. Initialization of the logging table in the database “CRM Tool”

  2. Initialization of the backup repository: repository will contain all the backups

  3. Cleaning and initialization of the reporting statement in the database “CRM Tool”

  4. Deploy the new customization to update the native data model of the new CRM organization: it will add the custom entities to the system entities and potentially update the relationships.

3.2 VARIABLES INITIALIZATION

  1. Initialization of the following variables: identifier of the “Organization” entity, identifier of the “Solution” entity, identifier of the “Business Unit” entity, identifier of the “System User” entity.

  2. Complete the identifier “System User” with its properties and the “User Settings” entity.

3.3 PREPARING CRM SECURITY MODEL

  1. Update the following entities in the new database (target): System User, Business Unit, Team.

  2. Complete the update with the following entities: organization, solution if needed.

3.4 END THE DATA PREPARATION

  1. Handle the indexes of the “target” database, executing a SQL command to disable the indexes.

  2. Logging the preparation in the database “CRM tool”.

  3. Create the backup of the preparation: “PREPARATION BEFORE DATA ORCHESTRATION”.

4. CORE MODEL DATA ORCHESTRATION: WAVE 0

SEQUENCE

ACTIONS

SEQUENCE 1

  1. Logging of the group 1: process has started.

  2. Preparing initial values: Organization and solution identifiers of the databases “source” and “target”; Organization name of the database “target”.

  3. Handling “Currency” entity if needed: Update “Organization” entity with the value of “currency”; Handle missing “Currency”.

  4. Handling “Owner” entity if needed: Handle the native service accounts “Integration” and “System”

  5. Generating report of the records.

  6. Logging of the group 1: process has ended.

  7. Creating the backing of the group 1.

SEQUENCE 2

  1. Logging of the group 2: process has started.

  2. Preparing initial values: Organization and solution identifiers of the databases “source” and “target”; Organization name of the database “target”the database “target”.

  3. Handling “System User” entity.

  4. Handling “Business Unit” and “Business Unit Map” entities.

  5. Handling “CurrencyClient update” entity, if needed: Update “Organization” entity with the value of “currency”; Handle missing “Currency”.

  6. Handling “Owner” entity if needed: Handle the native service accounts “Integration” and “System”Connection” and “Connection Role”.

  7. Handling custom entities if necessary, only because of the relationships.

  8. Generating report of the records.

  9. Logging of the group 12: process has ended.

  10. Creating the backing of the group 12.

SEQUENCE 2 3

  1. Logging of the group 23: process has started.

  2. Preparing initial values: Organization organization and solution identifiers of the database “target”.

  3. Handling “System User” entity.Handling “Business Unitentities related to the security model: “Role”, “Role Privilege”, “Internal Address”, including “User Settings” and “Business Unit Map” entities.Handling “Client update” entity, if needed.

  4. Handling “Connection” and “Connection Role”.

  5. Handling custom entities if necessary, only because of the relationships.

  6. Generating report of the records.

  7. Logging of the group 2: process has ended.

  8. Creating the backing of the group 2.

SEQUENCE 3

  1. Logging of the group 3: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to the security model: “Role”, “Role Privilege”, “Internal Address”, including “User Settings” and “Business Unit”, “System User Business Unit Entity Map”, “System User Principals”, “System User Role”, “System User Profiles”, “Queue”, “Queue Membership”.

  4. Handling the other entities: “Mail Merge Template”, “Metric”. “Site” entity, if needed, “UoMSchedule”, “UoM”, “Territory”, “Calendar”, “Calendar Rule” if needed.“System User Business Unit Entity Map”, “System User Principals”, “System User Role”, “System User Profiles”, “Queue”, “Queue Membership”.

  5. Handling the other entities: “Mail Merge Template”, “Metric”. “Site” entity, if needed, “UoMSchedule”, “UoM”, “Territory”, “Calendar”, “Calendar Rule” if needed.

  6. Handling custom entities if necessary, only because of the relationships.

  7. Generating report of the records.

  8. Logging of the group 3: process has ended.

  9. Creating the backing of the group 3.

SEQUENCE 4

  1. Logging of the group 4: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to “Calendar Rule” if not needed in sequence 3

  4. Handling custom entities if necessary, only because of the relationships.

  5. Generating report of the records.

  6. Logging of the group 34: process has ended.

  7. Creating the backing of the group 34.

SEQUENCE 45

  1. Logging of the group 45: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to “Calendar Rule” if not needed in sequence 3“User” entity: “User Settings”, “Team”, “Team Profiles”, “Team Membership”.

  4. Handling custom entities if necessary, only because of the relationships.

  5. Generating report of the records.

  6. Logging of the group 45: process has ended.

  7. Creating the backing of the group 45.

SEQUENCE 56

  1. Logging of the group 56: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to the “User” entity: “User SettingsQuery”, “TeamUser Query Visualization” if needed, “Team Profiles”, “Team Membership”User Entity UI Settings” if needed.

  4. Handling custom entities if necessary, only because of the relationships.

  5. Generating report of the records.

  6. Logging of the group 56: process has ended.

  7. Creating the backing of the group 6.

5.

...

Creating the backing of the group 5.

...

PRODUCT MODEL DATA ORCHESTRATION: WAVE 1

SEQUENCE

ACTIONS

SEQUENCE 7

  1. Logging of the group

6

  1. 7: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to the

“User”

  1. “Product” entity: “

User Query

  1. Production Association”, “Product Price Level”, “Price Level”, “Equipment”, “Product Substitute”, “Service”, “

User Query Visualization” if needed, “User Entity UI Settings” if needed

  1. Product Sales Literature”, if needed.

  2. Handing the other “system” entities regarding the relationships created in the customization process.

  3. Handling custom entities if necessary, only because of the relationships.

  4. Generating report of the records.

  5. Logging of the group

6

  1. 7: process has ended.

  2. Creating the backing of the group

6

  1. 7.

...

6.

...

CUSTOMER MODEL DATA ORCHESTRATION: WAVE

...

2

SEQUENCE

ACTIONS

SEQUENCE

7

8

  1. Logging of the group

7

  1. 8: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to the

“Product”

  1. “Contact” entity: “

Production Association

  1. Contact”, “

Product Price Level”, “Price Level”, “Equipment”, “Product Substitute”, “Service”, “Product Sales Literature”, if needed

  1. Contact Address”.

  2. Handing the other “system” entities regarding the relationships created in the customization process.

  3. Handling custom entities if necessary, only because of the relationships.

  4. Generating report of the records.

  5. Logging of the group

7

  1. 8: process has ended.

  2. Creating the backing of the group

7

  1. 8.

6. CUSTOMER MODEL DATA ORCHESTRATION: WAVE 2

SEQUENCE

ACTIONS

SEQUENCE 8SEQUENCE 9

  1. Logging of the group 89: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to the “Contact” “Account” entity: “ContactAccount”, “Account Address”, “Competitor”, “Contact Competitor Address”.

  4. Handing the other “system” entities regarding the relationships created in the customization process.

  5. Handling custom entities if necessary, only because of the relationships.

  6. Generating report of the records.

  7. Logging of the group 89: process has ended.

  8. Creating the backing of the group 8.

SEQUENCE 9

  1. .

  2. Creating the backing of the group 9.

7. CORE BUSINESS MODEL DATA ORCHESTRATION: WAVE 3

SEQUENCE

ACTIONS

SEQUENCE 10

  1. Logging of the group

9

  1. 10: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related to

the “Account” entity: “Account”, “Account Address”, “Competitor”, “Competitor Address

  1. the “Opportunity” entity: “Opportunity”, “Opportunity Close”, “Opportunity Product”, “Opportunity Sales Process”, “Lead”, “Lead Address”, “Quote”, “Quote Close”, “Quote Detail”, “Incident”, “Incident Resolution”, “Queue”, “Sales Order” “Sales Order Details”.

  2. Handing the other “system” entities regarding the relationships created in the customization process.

  3. Handling custom entities if necessary, only because of the relationships.

  4. Generating report of the records.

  5. Logging of the group

9

  1. 10: process has ended.

  2. Creating the backing of the group

9

  1. 10.

...

8.

...

ACTIVITY MODEL DATA ORCHESTRATION: WAVE

...

4

SEQUENCE

ACTIONS

SEQUENCE

10

11

  1. Logging of the group

10

  1. 11: process has started.

  2. Preparing initial values: organization and solution identifiers of the database “target”.

  3. Handling entities related

to the “Opportunity” entity: “Opportunity”, “Opportunity Close”, “Opportunity Product”, “Opportunity Sales Process”, “Lead”, “Lead Address”, “Quote”, “Quote Close”, “Quote Detail”, “Incident”, “Incident Resolution”, “Queue”, “Sales Order” “Sales Order Details”.

  • Handing the other “system” entities regarding the relationships created in the customization process.

    • Handling custom entities if necessary, only because of the relationships

    1. to the “Activity Pointer” entity – orchestrate with object type code: type opportunity, type incident, type appointment, type recurring appointment, type email, type fax, type letter, type phone call, type task, type campaign response, type bulk operation.

    2. Handing the other “system” entities: “Campaign Activity”, “Campaign Item”, “Campaign Activity Item”, “Email, “Email Hash”, “Email Search”, “Activity Party”.

    3. Handling the other system entities if needed: “User Entity Instance Data”, “User Entity UI Settings”.

    4. Handling custom activity entities if necessary, created as activity pointer.

    5. Generating report of the records.

  • Logging of the group 10: process has ended.

  • Creating the backing of the group 10.

    • 8. ACTIVITY MODEL DATA ORCHESTRATION: WAVE 4

    SEQUENCE

    ACTIONS

    SEQUENCE 11

    1. Logging of the group 11: process has started.

    2. Preparing initial values: organization and solution identifiers of the database “target”.

    3. Handling entities related to the “Activity Pointer” entity – orchestrate with object type code: type opportunity, type incident, type appointment, type recurring appointment, type email, type fax, type letter, type phone call, type task, type campaign response, type bulk operation.

    4. Handing the other “system” entities: “Campaign Activity”, “Campaign Item”, “Campaign Activity Item”, “Email, “Email Hash”, “Email Search”, “Activity Party”.

    5. Handling the other system entities if needed: “User Entity Instance Data”, “User Entity UI SettingsLogging of the group 11: process has ended.

    SEQUENCE 12

    1. Logging of the group 12: process has started.

    2. Preparing initial values: organization and solution identifiers of the database “target”.

    3. Handling entities related to the binary data: “Attachment”, “Activity Mime Attachment”, “Annotation”.

    4. Handling custom activity entities if necessary, created as activity pointerbecause of the relationships.

    5. Generating report of the records.

    6. Logging of the group 11: process has ended.

    SEQUENCE 12

    1. 12: process has ended.

    2. Creating the backing of the group 12.

    9. THE INTERNAL SYSTEM PROCESS DATA MODEL ORCHESTRATION: WAVE 5

    SEQUENCE

    ACTIONS

    SEQUENCE 13

    1. Logging of the group

    12

    1. 13: process has started.

    2. Preparing initial values

    : organization and solution identifiers of the database “target”.

  • Handling entities related to the binary data: “Attachment”, “Activity Mime Attachment”, “Annotation”.

    • Handling custom activity

    1. : organization and solution identifiers of the database “target”.

    2. Handling entities related to a data repository: “Audit”, “Principal Object Access”, “Principal Object Access Read Snapshot”, “Principal Object Attribute Access”, “Bulk Operation Log”, “Bulk Operation”, “Bulk Delete Operation”, “Bulk Delete Failure”, “Trace Log” “Trace Association”, “User Form”, “User Query Visualization”.

    3. Handling other system entities if necessary, because of the relationships.

    4. Generating report of the records.

    5. Logging of the group

    12

    1. 13: process has ended.

    2. Creating the backing of the group

    12

    1. 13.

    ...

    10. OTHER CUSTOM/SYSTEM BUSINESS DATA MODEL ORCHESTRATION: WAVE

    ...

    6

    SEQUENCE

    ACTIONS

    SEQUENCE

    13

    14

    1. Logging of the group

    13

    1. 14: process has started.

    2. Preparing initial

    values: organization and solution identifiers of the database “target”.

  • Handling entities related to a data repository: “Audit”, “Principal Object Access”, “Principal Object Access Read Snapshot”, “Principal Object Attribute Access”, “Bulk Operation Log”, “Bulk Operation”, “Bulk Delete Operation”, “Bulk Delete Failure”, “Trace Log” “Trace Association”, “User Form”, “User Query Visualization”.

    • Handling other system entities if necessary, because of the relationships

    1. values: organization and solution identifiers of the database “target”.

    2. Handling entities related to a system or native entity: “Resource”, “Resource Group”, etc...

    3. Handling entities related to a custom entity, created for business needs.

    4. Generating report of the records.

    5. Logging of the group

    13

    1. 14: process has ended.

    2. Creating the backing of the group

    13

    1. 14.

    10. OTHER CUSTOM/SYSTEM BUSINESS DATA MODEL ORCHESTRATION: WAVE 6

    SEQUENCE

    ACTIONS

    SEQUENCE 14

    SEQUENCE 15

    1. Logging of the group 1415: process has started.

    2. Preparing initial values: organization and solution identifiers of the database “target”.

    3. Handling entities related to a system or native entity: “Resource”, “Resource Group”, etcrepresenting a N-N relationship, entities not already handled: “Account Leads”, ...

    4. Handling entities related to a custom entity, created for business needsother custom entities if necessary, because of the relationships.

    5. Generating report of the records.records.

    6. Logging of the group 15: process has ended.

    7. Creating the backing of the group 15.

    11. AFTER DATA MODEL ORCHESTRATION: WAVE 7

    SEQUENCE

    ACTIONS

    SEQUENCE 16

    1. Logging of the

    group 14

    1. final process: process has

    ended

    1. started.

  • Creating the backing of the group 14.

    • SEQUENCE 15

    1. Handling the indexes: rebuild indexes.

    2. Logging of the

    group 15

    1. final process: process has

    started

    1. ended.

    Preparing initial values: organization and solution identifiers

    1. Creating the backing of the

    database “target”.

  • Handling entities representing a N-N relationship, entities not already handled: “Account Leads”, ...

  • Handling other custom entities if necessary, because of the relationships.

  • Generating report of the records.

  • Logging of the group 15: process has ended.

  • Creating the backing of the group 15.

    • 11. AFTER DATA MODEL ORCHESTRATION: WAVE 7

    SEQUENCE

    ACTIONS

    SEQUENCE 16

    1. Logging of the final process: process has started.

    2. Handling the indexes: rebuild indexes.

    3. Logging of the final process: process has ended.

    4. Creating the backing of the whole database: whole data migration.whole database: whole data migration.

    CONCLUSION

    Between each wave, we could integrate a validation process in order to validate the migration of the data. This validation process could be composed of 2 successive sub-processes: one process related to the deployment (letter A) and a second process related to functional tests (letter B).

    If the deployment or the functional tests do not succeed, the wave has to be fixed before moving forward. The reasons of the failure could be the followings:

    • Data or records that have been migrated are the source of the error.

    • Strategy related to the grouping, the ordering or the prioritizing has to be considered.

    ...