Increase Dynamics CRM Online Scribe Data Migration Performance

Slalom Consultant Jayson Goldinger

Jayson Goldinger is a Slalom Consultant based in Denver, CO, and a member of the Slalom National CRM practice. His work revolves around Microsoft Dynamics CRM including configuration, customization, report development, and integration. He is also recognized as a Scribe MVP.

For those of you who have done data migration or integration with Dynamics CRM Online, you know that the performance may be a challenge. I recently faced that challenge during a recent project.

I had approximately 52,000 account records with associated contacts to import. I also needed to do a check (Seek Step) against CRM to see if the record was already in CRM. I was using Scribe Insight with my source being Excel and my target was Dynamics CRM Online using the Dynamics CRM Adapter. On my first attempt my job ran for 9 hours and only imported approx. 15,000 records. At that rate it would have taken over 30 hours to complete. That rate is not really acceptable. It also potentially adds other issues like the connection being dropped after some period of time and then I would have to restart the integration. Some of my colleagues have said they have seen the Dynamics CRM connections dropped around the 8 hour mark.

The question then becomes how to combat this issue. Continue reading and I’ll explain how I was able to increase the performance from about 30 source records per minute to an average of 200 source records per minute. I say source records because in the job that I am discussing each source record may create one or two records.

First change I made was instead of doing a seek step against the CRM application, I did a seek step against a replicated SQL database. My client was using Scribe Online Replication Services (RS). If you are unfamiliar with this product, it is an application from Scribe that will simply replicate a source (i.e. Dynamics CRM, SQL, etc.) to a local SQL database. In this case the client was replicating CRM Online to a local SQL database for a Business Intelligence project. I was able to take advantage of this by doing my seek locally instead of querying the CRM Online application. This gained me quite a bit if efficiency on the records that were already in CRM and didn’t need to be imported but didn’t gain me enough efficiency on the records that needed to be inserted.

I gave this performance issue some more thought and came up with a solution that gave me the performance gain I needed. I decided to take advantage of Scribe’s multithreading capability. For those unfamiliar with this process I will explain. If you run a Scribe job directly from Workbench you are using a single thread. If you run a job using Microsoft Message Queuing (MSMQ) you can take advantage of multiple Scribe Message Processors to give you multiple threads.  Depending on the edition of Scribe that you have purchased you will either get up to 7 message processors for Standard or up to 63 message processors for Professional and Enterprise editions (1 processor gets reserved for other processes). I say up to X processors because you have the ability to limit the number of processors that Scribe will run.

Now to explain how to take advantage of this feature; it does require that you do a little extra work but the extra work will definitely pay off in performance gain. The first item required is that you have to get your source from an Excel file, Text, SQL or whatever into an MSMQ XML Message. The way that you do that is to create a Scribe dts where you use your source but change your target to XML and MSMQ. I am going to assume that you have some understanding of XML because you will need to create an XML file to be your schema file and sample file. Next create a Scribe dts where your source is XML (you can use same XML schema/sample file for both DTS files) and your target is the CRM adapter. Lastly you will need to create a job in the Scribe console to run the XML to CRM integration. I always like to create a Scribe Collaboration for each of my projects to keep things organized so I create my job in that collaboration but you can easily use the default collaboration if you don’t create collaborations for each project.

Once you have everything set up you can run your first job to populate the message queue and the second job will pick up the messages from the queue and integrate them. However, now it is able to take advantage of the multithreading. As I mentioned earlier, you can adjust the number of processors but what I noticed on the on the processors is that when I went from 16 to 24 I didn’t see much of a performance gain. I think that was because the server I was using for Scribe was just maxed out. I think if it were on better hardware I would have been able to get even better performance. On your next Scribe data migration with Dynamics CRM Online give multithreading a try and you too will realize the performance gain.

Good Luck!

About Jayson Goldinger
Jayson Goldinger is a Slalom Consultant based in Denver, CO and a member of the Slalom National CRM Team. His work revolves around Microsoft Dynamics CRM including configuration, customization, report development and integration. He is also recognized as a Scribe MVP.

3 Responses to Increase Dynamics CRM Online Scribe Data Migration Performance

  1. Spencer Riley says:

    Nice article Jayson, what was the hardware configuration for your Scribe server (memory and # processors)? Did you find any formula for optimum performance such as 4 threads per processor (e.g. 1 proc = 4 threads, 4 proc = 16 threads)?

  2. Hi Spencer,

    Thanks for this insightful post. Simego’s DS3 would only take 30 mins to move these in (including getting over the seek operation and mapping) without executemultiplerequests. Using the executemultiplerequests gives 3 minutes.

    https://www.simego.com/account/trialkey .

    We demonstrated this at a recent Microsoft Roadshow pushing 50000 records into Dynamics Online CRM in 10 minutes. I can post a video but it might be the most boring video ever.

    Thanks David

  3. Pingback: Blog :: Scribe Software » Scribe MVP Blog: Increase Dynamics CRM Online Scribe Data Migration Performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: