In the earlier post, we saw how we can use Skyvia’s query feature to execute SQL command against our Dynamics 365 CE
https://nishantrana.me/2019/10/25/execute-sql-select-insert-update-and-delete-command-on-dynamics-365-customer-engagement-data-using-skyvia-query/
In this post, we will cover how we can use Skyvia’s replication feature to create a copy of our cloud data i.e. Dynamics 365 Online data in a relational database like SQL Server and most interestingly keep it up to date automatically with basic configuration steps.
Creating replication of cloud data can be useful in different data integration scenarios and most importantly for reporting and data analysis need as with online we are limited with the use of Fetch XML.
The Skyvia’s replication service takes care of syncing Create, Update and Delete operation performed on Dynamics 365 Entity records with the destination SQL Database.
For this we need to Skyvia expects an object’s key as well as modifiedon, createdon fields to be included for replication.
For delete to work it expects, audit history to be enabled on that Entity.
In this post, we will take a simple example of replicating Contact entity records from Dynamics 365 Online to SQL Azure.
Login to Skyvia
https://app.skyvia.com/#/packages
Create the connections
Source Dynamics CRM Connection:
Destination SQL Azure Connection:
We have a database named SampleDB already created in SQL Azure.
With connections created, now let us create a new package of type replication
For the replication, we have specified source connection as the CRM connection and destination as the SQL Azure.
We have selected the option of Incremental Updates along with Create Tables and Drop Tables option which will delete if there is an existing table of the same name as the entity and will create a new one.
In the Select Objects section, we have a specified Contact entity. We can also select multiple entities for replication.
Edit task option in the selected entity let us specify the fields and filter criteria on the source.
We also have the option of scheduling that we will look at in the end of the post.
Save the package and click on Run to see it in action.
While it is running, the Run History section shows the details
It auto refreshes with all the details.
We can see the 228 records of contact records replicated in SQL Azure.
Now let us create a new contact and run the package again.
Here we have created a contact record as shown below in CRM.
On running the package, we can see that it has added a new record in the Run History
Back in SQL Azure also our count has increased to 229.
Now let us update one of the records in the source and run the package. The run history shows one of the records being updated.
Finally, let us delete a couple of records (9) from the source and run the package. (We need to make sure we have enabled Audit on Contact entity for delete to work)
Back in SQL Azure, we can see total record count decreased
Here we were running the package manually, however, in real-world scenarios we would be scheduling it.
For this, we can edit our package and navigate to the Schedule section and click on Set Schedule.
For e.g. we have scheduled here it to run every hour starting from 6 p.m. till 10 a.m.
On save, we can check the run history
Thus, we saw how easy it is to replicate the data from Dynamics 365 CE to our SQL Database using Skyvia’s Integration service.
To achieve the same using Scribe Online Replication Service
https://nishantrana.me/2019/01/09/using-scribe-online-replication-services-rs-to-replicate-dynamics-ce-data/
and KingswaySoft
http://www.kingswaysoft.com/blog/2017/04/04/How-to-Retrieve-Incremental-Changes-from-CRM-in-your-ETL-Process
Hope it helps..
Now the 2021 Release Wave 1 adds Quick actions to the search results as shown below.
