Dynamics Community Forum Thread Details

CDC from D365 FO to SQL Managed Instance

Hi Community,

We are currently evaluating a more efficient approach to synchronize data from Dynamics 365 Finance & Operations (D365 FO) to Azure SQL Managed Instance.

In the current architecture, data is exported from D365 FO to SQL MI using recurring data export/batch jobs. There are multiple entities/tables being synchronized, resulting in a relatively large number of scheduled batch jobs running in D365 FO.

The synchronization is currently scheduled approximately every 30 minutes to control the load on D365 FO. However, we are seeing several challenges:

A large number of recurring synchronization jobs need to be maintained.
During high-load/export periods, the synchronization can consume significant D365 FO resources.
Some tables can have hundreds or thousands of new/updated records between synchronization cycles.
If a synchronization job fails, recovering/resynchronizing the data can become expensive, especially for tables containing millions of records.
We would ideally like to process only the data that has changed since the previous successful synchronization instead of repeatedly querying/exporting larger datasets.
We also need better visibility and reliability to ensure that no transactions are missed.

We are therefore looking for a more incremental / CDC-like approach where changes from selected D365 FO entities/tables can be captured continuously or near real-time and delivered to an external integration layer, which can then synchronize the changes to SQL Managed Instance.

Could you please advise:

Is there a Microsoft-supported approach to capture incremental/row-level changes from D365 FO without relying heavily on recurring Data Export batch jobs?
Is there any CDC, change tracking, event, or API mechanism that can provide INSERT/UPDATE/DELETE changes for selected entities?
Would Business Events, Data Events, Dataverse, or another D365 FO capability be recommended for this data synchronization?
If an external event broker/integration platform is used, what would be the recommended way for D365 FO to publish these changes?
Are there any limitations or best practices regarding throughput, number of entities, or impact on D365 FO resources for this architecture?

Our target architecture would ideally look like:

D365 FO → Incremental Change/Event → Integration/Event Broker → SQL Managed Instance

instead of maintaining many recurring export jobs such as:

D365 FO → Multiple Scheduled Batch/Data Export Jobs → SQL Managed Instance

Thank you.

Categories:

Dynamics 365 Finance

Firstly one should consider whether it is necessary to run hundreds of recurring export jobs in F&O in order to keep Azure SQL synchronized.

If one needs near-real-time synchronization of certain F&O entities, I would start by looking at Data Events since they are able to publish create/update/delete changes for OData-enabled data entities, these changes then being sent via Azure Service Bus or Event Grid and processed in Azure SQL.

A simple pattern could be:

D365 F&O → Data Event → Service Bus/Event Grid → Azure Function → Azure SQL MI

It would mean getting rid of a great deal of the scheduled polling and the recurring batch jobs that are currently running in F&O.

But if the requirement is something similar to replicating a large number of tables containing millions of records, I wouldn't attempt to use Data Events for all situations. Instead, I would use Azure Synapse Link for F&O as the main replication method and then transfer the necessary data from the lake/Synapse layer to SQL MI.

Personally, I would probably use a hybrid approach:

* High-volume data/table replication → Synapse Link
* Business-critical near-real-time changes → Data Events + Service Bus
* Small/reference data → Scheduled incremental loads where appropriate

No matter which approach you decide on, you should also put in place an idempotency/watermark mechanism so that retries or duplicate events won't result in duplicate records being created in SQL MI.

I should also steer clear of having direct access to the underlying F&O AXDB for CDC, since maintaining and providing long-term support will be much easier if the integration is carried out through Microsoft's supported F&O data and integration frameworks.

In my opinion, the most significant improvement is to cease seeing each table as a separate batch integration issue and instead divide the data according to volume, latency, and business criticality before selecting the suitable integration pattern for each group.

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL