Skip to main content

Incremental Sync Mode

By default, Data Sync loads all records from the source and target before comparing them to identify differences. However, this can take a long time with large datasets. Incremental Mode is a stateless alternative that can be run multiple times without issues.

In Incremental Mode, you return a subset of data you want to check/sync against the target, such as all records created/modified today. Data Sync then loads the matching records from the target based on the key values of the source records. The same Data Compare process runs, and ADD/UPDATE actions are created as necessary.

Incremental Mode has some limitations:

  • Only single key columns are supported for most connectors (no composite keys). The exceptions to this are SQL Server, ADO.Net and Dynamics 365 (Dataverse).
  • DELETE actions cannot be performed. Since we are only seeing a subset of the Data, there is no way to work out DELETE actions.
  • Source records should be less than 100k records. As we need to match the keys on the target this could be slow, however we use batching and multiple threads to request all the records in parallel to speed things up.
  • The data connector must support Incremental Mode.

Supported Data Connectors​

Data Sync supports several data connectors for Incremental Mode, including:

  • Microsoft SQL Server
  • ODBC Connections
  • OleDb Connection
  • Microsoft Dynamics 365 (Entities)
  • Microsoft SharePoint (Client API)
  • Microsoft SharePoint Online (Office 365)
  • Azure Table Storage
  • Amazon S3
  • Podio Items
  • Simego WebStore List
  • Active Directory V2

The data connectors that support composite keys are SQL Server, ADO.net and Dynamics 365 (Dataverse).

Enable Incremental Mode​

To enable Incremental Mode, set the Project SyncMode to SyncAtoBIncremental from the properties section in the file menu or select Incremental from the Sync Mode dropdown.

Set Incremental Mode

Then, Data Sync will only load matching records from the target based on the Key column selection in the schema map.

It's recommended to use Incremental Mode when the target dataset contains many thousands or millions of records, and the source dataset is relatively small (<20k records). If the source returns a large dataset, Incremental Mode may not be optimal due to the round trips required to the target to retrieve the records. However, Data Sync optimizes this using batching and multiple threads where possible.

Recommendations​

To optimize running your project in Incremental Mode:

  • Ensure the Key column in the target is an indexed field.
  • Lookups may still cause issues if the Target Lookup Data Source is large, consider using LOOKUPAINCREMENTAL and LOOKUPBINCREMENTAL. However since these round-trip on each lookup value it is recommend only when your source contains very few records.
  • Use Project Automation to re-write your source filter at runtime to keep the source records small.

For example the code below shows using a FetchXML filter on data source A to only return items modified today:

public override void Start()
{
    DataSourceA.FetchXmlFilterExpression = DataSourceA.GetFetchFilterXmlForModifiedSince(DateTime.Today);
}

Using Incremental Mode with Project Automation​

This mode of operation would usually be used with a View or FetchXMLFilter Expression that limits the source records.

When Incremental Mode is used with Ouvvi you can dynamically adjust the source filtering based on a value supplied by the server. The server will inject Project Properties into the Project Properties collection.

For example, the property Auto_LastSuccessfulRun value is the time-stamp from Ouvvi of when the project was last run. This is automatically added to the project when it is opened from Ouvvi.

You can view the project properties by going to File > Properties > Properties, and then clicking onto the ellipsis (...) to open the collection manager.

View Project Properties

If we click onto the Auto_LastSuccessfulRun property we can see the last time the project was run.

Property Collection

You can then combine this with Project Automation to adjust your data source properties at runtime, for example to update the WHERE clause of a SQL Provider to use the LastSuccessfulRun value, you could use the code below:

DataSourceA.CommandWhere = string.Format("DateModified >= '{0}'", Properties["Auto_LastSuccessfulRun"]);

Project Automation