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

(0) Share

Report

Posted on by nkhemji

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

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Charles Abi Khirs 3,569 on at

Like (0)

Report

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

Was this reply helpful? Yes No
nkhemji 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Charles Abi Khirs 3,569 on at

Like (0)

Report

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.

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

Like (1)

Report

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)

Was this reply helpful? Yes No
nkhemji 2 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Henry Travis 5 on at

Like (0)

Report

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.

Was this reply helpful? Yes No