CRM ON-Premise External Database Integration

(0) Share

Report

Posted on by Community Member

I have a requirement to process 10's millions of records on a regular basis and would like to use an external SQL Server database to do the heavy lifting and then take result sets back into CRM. The issue I have is the speed of transferring the data. We have tried using Powershell but that is too slow Next we are profiling TPL to do the same task and then lastly using Scribe. Has anyone developed a service that has a similar pattern and if so what woudl they suggest as a good way of moving data between CRM and SQL Server say a million rows a day.

Thnaks for any help

Mike

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

gupta.ambar2009@gma... 797 on at

Like (0)

Report
Copy link

Link copied!

check for scribe tool

Was this reply helpful? Yes No
Suggested answer

M I Mostafa 4,257 on at

Like (0)

Report
Copy link

Link copied!

Hi Mike,

If you have your data in an external SQL Server Database, then you might want to consider SSIS with the Kingswaysoft connector for Dynamics. The advantage here is that you will have a native SQL component (SSIS) writing directly to CRM through the SSIS connector to Dynamics (Kingswaysoft).

Scribe could also be a good option. Personally, I had a requirement many years ago to migrate 22 million records over 1 weekend. I used scribe insight (on-premise version) and after several tries, iterations and permutations, I managed to get it into CRM in under 72 hours for go live. This was obviously for a one off migration not continuous data load.

The main approach that helped was having Scribe installed on the same server as the CRM on-premise server all on the same supa *** server (64GB RAM, fast processor, etc.). This server was used for the migration only and as soon as all 22 million records were transferred, we took a copy of the CRM database and added it as a new organisation instance into the production servers. The advantage of doing the whole migration on one server only is that you minimise any reliance on network connections. Even if you have the two servers sitting next to each other there will always be network latency while a fast rpm SSD disk drive with high RAM, processor configuration can be improve performance massively.

I realise this second option is for data migration while your requirement is for regular updates but I thought I'll share my experience as it may help you drive some ideas out of this.

Hope this helps! Please mark as verified answer if this response has helped you.

Was this reply helpful? Yes No
David Jennaway 14,065 on at

Like (0)

Report
Copy link

Link copied!
To get data from CRM to SQL, it will be faster to use SQL commands rather than the CRM API. It can also be noticeably faster (unsupported, though reasonably safe) to use the underlying SQL views (e.g. Account) rather than the filtered views (e.g. FilteredAccount)

To write data back to CRM, the only safe option is to use the CRM API. Which tool you use doesn't make much difference, other than ensuring you can do the following optimisations:

Use ExecuteMultiple to submit multiple requests in one go

Split your operations so that you can run different updates in parallel - I've had good performance running up to 10 packages in parallel, and you may be able to run more in parallel, depending on your hardware

If the target entities have plugins or workflows attached, and you can disable them during the upload to CRM, then that can also make a difference. You should be able to include steps to disable plugins or workflows at the start of the upload process, then reenable them at the end

Was this reply helpful? Yes No
Suggested answer

M I Mostafa 4,257 on at

Like (0)

Report
Copy link

Link copied!

Hi Mike,

Just to add to my points, Scribe also has a "bulk insert" features which speeds writing to CRM from personal experience.

Hope this helps!

Was this reply helpful? Yes No