Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Best practice to repli...
Microsoft Dynamics CRM (Archived)

Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

(0) ShareShare
ReportReport
Posted on by nkhemji 2

Hi,

just wondering what is the best option to create replicated data of all or selected entities to on-prem SQL server.

So far I have used SSIS with Kingsway components to move the data. However, how can we create tables structure for all or selected entities automatically? or how the Optionset will work? what are other things that need to be taken care of?

Do I have to create dataflows for each entity manually or there's a tool/script that can help?


Please let me know from your experience the best way to achieve this?

Note: I know about the data export service but we have already ruled this option.

*This post is locked for comments

  • Henry Travis Profile Picture
    Henry Travis 5 on at
    RE: Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

    Try Skyvia cloud platform for replication

    It will create schema automatically. In addition, it is a completely no-coding solution with a user-friendly interface.

  • nkhemji Profile Picture
    nkhemji 2 on at
    RE: Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

    Thanks for the reply David.

    Yeah I anticipated this issues so I asked if there could be an easy way to automate the development. So seems like there's none. :(

    Well... I didn't get the complete point on Optionset? Do we not have to create all optionsets as separate tables and then link the records?

  • David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

    I would still consider the Data Export Service. You could use the Data Export Service to get data from CRM Online to an Azure SQL Server. Then you could have a VPN between the Azure SQL Server and your OnPremise SQL Server, and use SQL replication to copy the data from the Azure SQL Server to OnPremise. This may seem a little complex, but it means all the per-entity setup and data transfer can be done via configuration.

    If you don't want to do this, I'd use SSIS, but it's more development effort. You'd need to create a data flow per entity, using something like KingswaySoft as the source, and an OLEDB Destination as the destination. When you add the OLEDB Destination, VisualStudio gives the option to create the table, based on the fields from the source. For optionset values, the simplest option is to copy both the optionset value (e.g. prioritycode) and the associated virtual attribute for the label (e.g. prioritycodename)

  • Suggested answer
    Charles Abi Khirs Profile Picture
    Charles Abi Khirs 3,569 on at
    RE: Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

    You can create a support request  CRM Online Database Backup with Microsoft. Check This link: docs.microsoft.com/.../hh127043(v=crm.6)

    For daily basis scenario, it's better to do it using Scribe or SSIS.

  • nkhemji Profile Picture
    nkhemji 2 on at
    RE: Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

    No this is not on-prem environment. Need to copy from online to on-prem on daily basis.. possibly only new and change records

  • Suggested answer
    Charles Abi Khirs Profile Picture
    Charles Abi Khirs 3,569 on at
    RE: Best practice to replicate CRM (dynamic 365) data to on-prem SQL server

    Hello,

    Since it is on-premises environment and since you need your table structure, data... to be replicated, you can

    1. Take a backup of your <OrgName>_MSCRM database

    2. Restore your database into a new Database

    3. Use deployment Manager to Import Organization

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans