Change Tracking in Dynamics 365 FO

 

This blog post is intended to give you some idea on how Change Tracking is used when exporting data through entities in Data Management.

It is not going to explain the basic principles of SQL Server Change Tracking (see About Change Tracking) and how the simple task of configuring the Data Management export projects, which I assume you either already know or can read about elsewhere (see Enable Change Tracking for entities).

I will try to explain what happens behind the scenes when the configuration is enabled or changed, and when data is exported.

Tables configured with Change Tracking

Let us start by investigating which tables are enabled for Change Tracking in the SQL database:

SELECT
   sct1.name AS CT_schema,
   sot1.name AS CT_table,
   ps1.row_count AS CT_rows,
   ps1.reserved_page_count*8./1024. AS CT_reserved_MB,
   sct2.name AS tracked_schema,
   sot2.name AS tracked_name,
   ps2.row_count AS tracked_rows,
   ps2.reserved_page_count*8./1024. AS tracked_base_table_MB,
   change_tracking_min_valid_version(sot2.object_id) AS min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 ON it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 ON sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 ON it.object_id = ps1. object_id AND ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 ON it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 ON sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 ON sot2.object_id = ps2. object_id AND ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210)
ORDER BY Tracked_Name

This script lists all tables in the current database which have been activated for change tracking. We see that only the CustGroup table is currently enabled.

Details about the tracked changes are kept in two places:

• Syscommittab holds information on all changes on a high level and a pointer to the details in the side tables
• Side tables with the naming conversion “change_tracking_[unique ID]”

Change Tracking level on data entities

Enabling a data entity for Change Tracking enables the individual tables in the entity based on the Change Tracking level.

Consider the data entity “Fleet Management Rentals” (FMRentalEntity) which consist of three tables: FMRental, FMCustomer and FMVehicle

Primary table

Enabling Change Tracking for Primary table on the entity only enables the top-level table (FMRental) of the entity data source.

Entire entity

Enabling Change Tracking for Entire entity enables all tables of the entity data source.

Custom

Enabling Change Tracking for Custom query enables the tables of the entity data source that are added by code to the default Change Tracking query.

The sample below adds the two tables FMRental and FMCustomer to the query, making them the ones that are change tracked. This means only changes to these two tables will trigger and export of the FMRental entity records.

Disabling change tracking

Disabling Change Tracking on the entity “Fleet Management Rentals” (FMRentalEntity), will remove all tables in the entity data source to a certain point. I experienced that sometimes not all tracked tables are removed. Not sure which tables are left in for tracking, but it seems to be tables possibly tracked by other entities, like DirPartyTable, InventDim etc.

Change Tracking on incremental export

Adding a data entity to a Data Management export project for the first time with Default Refresh Type = Incremental and running the initial export, creates a record in the table DMFEntityDbSyncVersion. This record holds the latest synchronization version for the current data entity in the Definition group in each legal entity. So, the key is: Definition group, Data area id, Data entity name.

After each export, the synchronization version number is set to the current database Change Tracking version.

The CHANGE_TRACKING_CURRENT_VERSION() returns the highest change tracking number for the database, so setting the definition group synchronization version to this number, will make sure that no older records will be re-exported.

So, next time the export is run, the current synchronization version for the data entity is validated against the change tracking side table versions for each table in the entity data source marked for Change Tracking. If records exist with higher version numbers they are considered changed and will be exported.

Querying current tracked changes

After creating two new rentals in the FMRental table we can check out what is expected to be exported in the next run by issuing the following script:

DECLARE @last_sync_version bigint; 
SET @last_sync_version = (SELECT AXSYNCVERSION FROM DMFEntityDBSyncVersion
                 WHERE DEFINITIONGROUPNAME = 'Fleet_Rental_Export')
SELECT @last_sync_version AS LAST_SYNC_VERSION,SYS_CHANGE_VERSION, FMRental.* FROM FMRental
RIGHT JOIN CHANGETABLE(CHANGES FMRental, null) AS CT ON FMRental.RECID = CT.RECID

First the @last_sync_version variable is initialized with the current synchronization version that the definition group has. This is used to compare against the current tracked changes version number.

As we can see the LAST_SYNC_VERSION is only higher than the last two rows in the change list, so we will expect two rows exported on the next run.

Again, the definition group is marked with the current highest change tracking number, so the same records are not exported next time.

Observations

A data entity Change Tracking starts at the moment of enablement, which means that all tables created or update before this point is not tracked. That is why initial export is always a Full push. It is not possible to manually add records to the change tracking, unless you somehow change them through D365

          Deletion is not supported for plain data entity exports like files, which makes sense since how would you export a record that has been deleted to a file? Tracking deletions is supported for export to Bring You Own Database (BYOD)