The most common use case to export Dynamics 365 or Common Data Service data to an external database/datastore is to have more control over the data (without going through API route) with the ability to run analytics such as Power BI Reporting, Machine Learning, Data Warehousing, Integration, etc.
For analytics, we can also use our Microsoft Dynamics 365 AI apps that use Dynamics 365 data as a source
Below listing down few of the options –
|
Data Export Service – Configuration |
It is a free Add on-service provided by Microsoft for exporting Dynamics 365 or CDS Data to Microsoft Azure SQL Database or Microsoft Azure SQL Server on Microsoft Azure Virtual Machine. Microsoft recommends Azure SQL Database Premium P1 or a better plan for Data Export Service https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database Notes on Data Export Service – https://nishantrana.me/2018/10/04/my-notes-on-dynamics-365-data-export-service/ |
|
Export to Data Lake Service – Configuration |
The service enables continuous replication of CDS Data to Azure Data Lake Gen 2. https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/export-to-data-lake Few posts on Azure Data Lake |
|
Azure Data Factory – GUI based integration tool |
Cloud-based integration service. https://docs.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365 Check out the informative posts on Azure Data Factory –https://dynamicscrmgirl.wordpress.com/tag/azure-data-factory/ We can also run SSIS Packages on Azure Data Factory https://nishantrana.me/2020/10/21/posts-on-azure-data-factory/ |
|
Scribe Online Replication Services – GUI based integration tool |
Cloud-based integration service. Another option is to use Scribe Insight https://trellispoint.com/quickly-copy-dynamics-365-online-data-to-sql-server-using-scribe-insight/ |
|
Skyvia’s Data Integration service Informatica |
Cloud-based integration service. Another GUI based integration tool that can be considered is Informatica |
|
KingswaySoft Integration Components – SSIS |
Leading data integration software – provide SSIS components to integrate applications/databases. Another SSIS based option is CozyRoc https://www.cozyroc.com/ssis/dynamics-crm Or we can use Script Component instead of using 3rd party components/ tool https://nishantrana.me/2020/10/19/connect-to-dynamics-365-through-script-component-ssis/ More on SSIS and Dynamics 365 https://nishantrana.me/2020/10/16/ssis-kingswaysoft-and-dynamics-365/ |
|
Use SQL to query CDS data (Preview) |
The capability to query CDS Data. https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query Check out all the articles on CDS T-SQL https://markcarrington.dev/category/msdyn365/t-sql/ https://nishantrana.me/2020/05/21/setting-up-using-sql-to-query-data-in-dynamics-365-preview/ |
|
Query CDS data using Web API / Organization Service – Development |
Write a custom tool using Dynamics 365 APIs https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api |
Hope it helps..
Nishant Rana's Weblog 
One thought on “Replicate / Export Dynamics 365 (CDS) data – different options”