More than a problem in itself, the article describes the strategy I used for data migration from CRM 2011 on premise to CRM 2016 online. With the blog, I intend to present a data migration strategy which may serve as a guide for migrating data.
In my last project engagement we had to migrate data from CRM 2011 on premise to CRM 2016 Online. The total size of the data which had to be migrated was close to 50 GB. Most of the entities that were to be migrated had 1: N, N: 1 and N: N relations which had to be migrated as well.
Using the already available tools like Kingsway it’s easy to migrate the data across to the destination organization. However we still need to think of the mentioned below points while designing our packages
- Maintainability of packages – Designing the packages in a way that
- Avoids creating duplicate records in the CRM organization.
- If a package is stopped and rerun, it should only retrieve the records which are yet to be created in Dynamics.
- Provide error logging for the records which are not inserted in the organization.
- Increases the overall speed of packages. That is number of operations that are being done in the package each second.
- Maintaining the sanity of Data, i.e. relations between the migrated records should be maintained.
- In some cases it’s also important to map the created records to the same owner as in the source organization.
Designing the Packages
a) Setting up Master Data – Most CRM implementations have certain entities which have very few records (no 1: N, N: 1 or N: N). the record count is also like less than 1000. We can easily utilize CRM out of box import excel or csv for this.
b) Outlining the hierarchy of records – While migrating data, we need to ensure that we must migrate the parent records first. Otherwise the reference field in child records will not be set. For example before migrating activities we must migrate the parent entities like contact account, opportunities etc.
c) Creating a Staging DB having temporary success and error tables – To ensure that we do not end up creating duplicate records in the target organization, we can create two tables’ success and error. Only required columns like record unique GUID, name identifier or error message can be added to the tables.
The idea is to do a join between the source CRM table and this success table. This will help us in identifying the source records which have not been inserted in target CRM organization. Mentioned below Visio diagram explains the flow in general.
A Note on Creating Retrieve Queries on Source CRM Database
In some cases, the user under whose context the packages are executing, might not be a user in the source CRM organization. Due to this any query on Filtered Views, will return zero records. Due to this, the Query needs to be run under the context of a different user. Mentioned below is the way this could be accomplished
DECLARE @uid uniqueidentifier
SET @uid = convert(uniqueidentifier, ‘64061C21-DE18-E611-93E1-0050569A0D99’)
SET CONTEXT_INFO @uid
Select * from FilteredCompetitor
64061C21-DE18-E611-93E1-0050569A0D99 is the unique ID of the admin user in the Source DB. We can get it by just executing a Select query on table “SystemUser”
A Note on Increasing the efficiency of packages
When the project is being executed, there are several counters available wherein you can check and monitor the bottleneck.
Just from the perspective of SSIS, there is a component Balanced Data Distributor. The idea of this component is to distribute the source into equal sub streams. Each sub stream can then be added to the custom code component or Kingsway software do perform the CRUD operations in CRM.
A Note on Online Storage Limit
Till Dynamics CRM 2016, by default all instances are allotted a memory space of 5 GB. Therefore it is recommended to consult and filter out unnecessary records. For example we may not need closed tasks, phone calls, appointments along with their attachments.
A Note on Mapping the Owner Field for records
By default all the records created in the target organization will be assigned to the owner under whose context we are executing the organization request.
To assign the records to their respective users, we need to firstly create the users in target organization and then do a retrieve the user id using a condition on their name. After getting the ownerid we can then set the ownerid field
A Note on maintaining N: N relations
As we need record ids for each of the associated records, we must do this operation in a separate package. We can do this by doing a lookup operation to success tables of each of the migrated entities and then executing the associate request.