locked
Data Replication to/from Dynamics CRM RRS feed

  • Question

  • We have a custom written Dynamics CRM Application with a complex data model. I would like to be able to replicate data in near real time to/from CRM. The replication target outside of CRM is a SQL Server staging database.

    I am not the CRM expert, but I'm told that we cannot read/write directly to the CRM database itself and must instead go through CRM somehow, but the capability for replication does not exist and a 3rd party tool may be required.

    I'm trying to understand what the approaches and options are. Any advice would be greatly appreciate. Today the application is on Dynamics CRM 2011.

    Monday, May 5, 2014 5:55 PM

All replies

  • Yes, it is not supported to changes CRM database tables directly and these action will not be supported by Microsoft.

    But having said this I have seen many implementation where company had to directly update CRM tables and if you go thorough proper testing and have got valid reasons for doing this for e.g. if you loading/updating over 2 millions records every day then if you go with standard approach your data load could take some time.

     


    MayankP
    My Blog
    Follow Me on Twitter

    Monday, May 5, 2014 6:31 PM
    Answerer
  • The best supported method is through the API.  This also means that if you want/need, you can apply business logic to the records as they get added/updated in CRM.  There are tools like Scribe that are designed to handle the complexities of replication with multiple tables, dependencies, etc. However, if you're proficient with SSIS, you can do it there, as well.

    As MayankP mentioned, it is possible to write directly to the CRM database, but I definitely would warn of unintended consequences.  Especially because the database model itself changed between CRM 2011 and 2013.  So if you set up a sync to CRM 2011 (I don't know what version of CRM you are on), you will want to be prepared to rewrite it for a CRM 2013 upgrade.  (The old model had the base columns and the user-created columns in two different tables per entity, CRM 2013 merged them all into one table per entity).


    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    Monday, May 5, 2014 7:09 PM
  • Can we assume that we are going to use the Dynamics CRM API rather than directly writing to tables?

    There are several approaches to this problem but all of them must solve the following:

    • Detecting when changes have been made to the CRM or SQL Data or how often to replicate if you choose to schedule.
    • Identification of new/updated/changed rows that have not been replicated.
    • Identify changed attributes / columns.
    • Identify ownership of new records or changes.
    • Potentially mapping of Active/Inactive states to delete records.
    • Recovery from either CRM un-availability, restore and SQL un-availability or restore. From this I mean, when you start how are you going to bring both sides in sync? If you restore the SQL server due to a failure (or upgrade it) how are you going to bring it up to date? If you restore the CRM database server due to a failure (or upgrade it) how are you going to bring that up to date.

    If performance is an issue you may have to combine multi-threading with Dynamics CRM batch API.

    I have assisted and developed a lot of near real-time integration solutions. SSIS, Scribe can achieve this but I am biased because I work for a third-party software vendor www.simego.com which integrates CRM into other platforms.

    I recently wrote a blog article on Real-Time / Near Real-Time integration with CRM discussing some of the issues and challenges:

    http://www.simego.com/Blog/2013/11/Dynamics-CRM-Real-Time-Integration


    Tuesday, May 6, 2014 9:56 AM