Azure data sync - Bulk inserts incorrect data which are not in on-prem db

Question

We are using azure data sync to sync data from on-prem to azure sql , During initial initial synchronisation , there are incorrect data inserted in azure sql db , these records do not exists in on-prem db. Any reason why this is happening?

Answer 1

Hi Aparna Manoharan,

Thanks for reaching out to Microsoft Q&A.

The issue of incorrect data being inserted into azure sql database during the initial synchronization with azure data sync can stem from several factors related to how azure data sync operates and the setup of your databases.

Possible reasons for incorrect data insertion might include the following:

Schema conflicts:

if there are tables with the same name but different schemas included in the sync, this can lead to erroneous data. Azure data sync uses the same tracking tables for tables with identical names across different schemas, causing data from one table to be incorrectly applied to another.

Data sync tracking issues:

• Azure data sync relies on tracking tables and triggers to manage data changes. If these tracking objects are missing or incorrectly configured, it can lead to the insertion of incorrect data. For example, if the tracking table for a specific table is deleted or not properly set up, it can cause sync operations to misbehave.

Verify if a full synchronization or an incremental sync was performed and if the correct process was followed.

Ensure that the sync frequency settings are properly configured to avoid synchronization overlaps or conflicts.

Bulk insert behavior:

during the initial sync, azure data sync performs bulk inserts. If the target azure sql database is not empty, it may attempt to insert records that already exist or are incorrectly identified, leading to data discrepancies. Ensuring that the target tables are empty before the initial sync can help mitigate this issue.

Data modification timing:

if there are concurrent modifications to the same data in both the on-premises and azure databases, this can lead to conflicts and incorrect data being inserted. Azure data sync processes changes based on timestamps, and if there are overlapping modifications, it can result in unexpected data states.

Provisioning and configuration errors:

if the sync group was not properly configured, or if there were changes made to the database schema (like deleting and recreating tables), this can disrupt the sync process. Each table must be removed from the sync group before making schema changes and then re-added to ensure proper tracking.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

@Aparna Manoharan Thank you for reaching out.

Could you please check the below

If both tables have the same PK.

If both tables have different indexes, or if Primary key is not disabled, or don't have one at all. You need to make sure that both tables are absolutely the same, from columns, keys and data types.

Regards,

Oury