In many enterprise environments, Dynamics 365 Finance and Operations (F&O) serves as the core transactional system, but it’s rarely the only one. Large organizations often operate across multiple legal entities, and integrating or reporting on massive datasets becomes a recurring challenge.
When working in environments with 150+ legal entities and millions of daily transactions, getting that volume of data out of F&O for reporting, analytics, and integrations can be a time-consuming process. This is especially true when using Bring Your Own Database (BYOD) exports to replicate Dynamics 365 data into an on-premises SQL Server for ETL, data transformation, or downstream consumption.
Problem Statement
Enterprises rely heavily on detailed transaction data for financial reporting, audit compliance, data warehouse feeds, and integrations with external systems. The GeneralJournalAccountEntryEntity (GJAE) is one of the largest and most complex entities in F&O, representing detailed general ledger transactions.
Challenges arise when exporting tens of millions of rows from F&O to an on-premises SQL Server. Exports can run for days or weeks, transaction logs can grow uncontrollably, and system performance can degrade due to inefficient indexing and I/O bottlenecks. Without proper configuration, these high-volume exports cause reporting delays, missed SLAs, and increased system load.
Solution Overview
Efficiently exporting large datasets from F&O to on-premises SQL Server requires optimizing the database structure, improving performance, and dividing export workloads into manageable chunks. Below are proven strategies to enhance throughput and stability.
1. Database Configuration: Build for Throughput
For high-volume data exports, SQL Server disk performance and transaction log handling are critical.
If using Azure SQL:
Provision the Hyperscale tier for the BYOD database to handle large transaction log throughput and IOPS.
If using On-Premises SQL Server:
Allocate high disk throughput using SSD or SAN storage.
Keep data and log files on separate drives to reduce I/O contention.
Set autogrowth to a fixed MB value instead of a percentage.
Enable Instant File Initialization to minimize delays during autogrowth.
2. Index Strategy: Keep It Lean (With Important Consideration for Incremental Loads)
By default, the Data Management Framework (DMF) creates both clustered and non-clustered indexes. While non-clustered indexes improve query performance, they significantly slow down inserts — which are the primary operation during large exports.
Recommendations:
Drop all non-clustered indexes before export on the GeneralJournalAccountEntryEntity staging table.
Keep only the clustered index during export.
Recreate non-clustered indexes after export if needed for reporting or transformations.
Important: Impact on Incremental Loads
In high-volume scenarios, teams often drop indexes — and in some cases, recreate or modify staging table structures — to maximize insert performance.
However, incremental load strategies may not work reliably if indexes are dropped from the staging table.
Incremental exports in BYOD rely on change tracking and indexed fields to efficiently detect and process delta records. If the required indexes are removed:
Change detection can degrade.
Incremental loads may become inconsistent.
Full reloads may be triggered unintentionally.
Performance benefits of incremental export may be lost.
Recommendation:
If your integration depends on incremental load:
Validate whether DMF change tracking relies on specific indexes.
Test incremental exports after index changes.
Consider maintaining minimal required indexes for delta processing.
Use full-load segmentation (by fiscal year or period) instead of incremental if index removal is mandatory for performance.
In some enterprise cases, teams deliberately switch to segmented full loads rather than incremental exports due to staging table performance constraints.
3. Enable Row Compression
For large data volumes, enabling row compression reduces I/O and storage needs with minimal CPU overhead.
SQL Example:
4. Data Export: Split and Conquer
Running a single full export of a massive table like GJAE often results in timeouts or memory issues. Instead, split exports by logical segments such as fiscal year or accounting period.
Example approach:
Export data by fiscal year or by quarter.
Create multiple DMF export jobs with filters on AccountingDate.
Run the jobs sequentially or in parallel based on your SQL infrastructure.
This approach improves manageability, reduces failures, and simplifies ETL scheduling.
Recommended Structured Approach
A structured recommendation model for optimizing high-volume exports:
Step 1: Infrastructure – Use high-IOPS disks, separate log drives, and fixed autogrowth.
Step 2: Index Management – Drop non-clustered indexes before export and keep only clustered indexes.
Step 3: Compression – Enable row compression on the clustered index.
Step 4: Export Strategy – Use multiple DMF export jobs with date filters.
Step 5: Transaction Log – Configure frequent log backups and adequate log size.
Step 6: ETL & Scheduling – Use SQL Agent, Power Automate, or Azure Data Factory for orchestration.
Step 7: Validate Incremental Behavior – If using incremental loads, verify compatibility after index changes.
Final Thoughts
In large enterprise environments, optimizing data exports from Dynamics 365 Finance and Operations is an ongoing process rather than a one-time setup.
By fine-tuning indexes, enabling compression, carefully evaluating incremental load behavior, and splitting exports intelligently, organizations can achieve faster, more reliable data pipelines from F&O to on-premises SQL Server.
After applying these recommendations, export times can be reduced from weeks to just a few days, with improved system performance and stability.
Special thanks to Brian Tubb for his insights and recommendations shared in the Yammer group. I implemented and tested his solution successfully in my environment with excellent results.
