Azure Data Sync to on-prem SQL succeeded but the destination table has old and new records

Hi @ Aaron Wong

Thanks for reaching out! I understand your concern — it's confusing when the sync says “Success” but old records are still sitting in your on-prem database.

What’s likely happening is this: Azure SQL Data Sync doesn’t automatically delete records on the destination side (your on-prem SQL Server) unless that option is specifically enabled. So if a row is deleted in Azure but deletion syncing isn't turned on or properly tracking changes, it’ll stay in the on-prem table.

Here’s what we recommend checking:

Make sure your sync group is set to sync deletions. This setting isn’t on by default.

Confirm that change tracking is turned on for the table in Azure. Sync relies on this to know when things have changed—including deletes.

If everything looks right but the problem continues, a sync reinitialization might help—it resets and fully re-syncs everything from scratch.

In the meantime, if you need to clean things up quickly, you can manually compare the Azure and on-prem tables and delete any extra rows on the on-prem side.

Also, if your system depends heavily on deletions being reflected accurately, you might want to consider using tools like Azure Data Factory for more control.

Hope this helps. Do let us know if you any further queries.

Hi Sai Raghunath, Thank you for the response. The sync group has been set up for more than a 4-5 months and data has been syncing properly so far, the issue only happened couple of days ago.

Also, we only see the following options in the sync group properties on Azure portal. Can you tell us where the additional properties can be setup, to sync deleted records etc.?

Hi @ Aaron Wong

Currently, Azure SQL Data Sync does not provide a direct option in the portal settings to synchronize deleted records from your Azure SQL Database to your on-premises SQL Server. This limitation arises because the service primarily focuses on synchronizing insert and update operations.

To ensure that deletions are reflected in your on-premises database, consider the following steps:

1.Enable Change Tracking on Azure SQL Database:

Change Tracking allows the service to detect changes, including deletions.

Use the following SQL commands to enable Change Tracking:

ALTER DATABASE [YourDatabase] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE [YourTable] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

2.Configure Sync Group to Include Deletions:

While the portal interface does not explicitly mention an option to sync deletions, enabling Change Tracking as described above allows the service to detect and synchronize deletions.

3.Set Conflict Resolution Policy:

In the Sync Group settings, choose a conflict resolution policy:

Hub Wins: Changes from the Azure SQL Database take precedence.

Member Wins: Changes from the on-premises SQL Server take precedence.

Select the policy that aligns with your data governance requirements.

4.Perform a Sync Reinitialization:

If issues persist, consider reinitializing the sync group.

This process resets the synchronization state and can help resolve inconsistencies.

Hi @ Aaron Wong

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.