Azure Data Sync : Error while adding a table back to sync group

Question

Azure Data Sync : Error while adding a table back to sync group

Aaron Wong 0

We have an Azure Sync Group with multiple tables being synced from Azure SQL DB to an on-prem SQL DB. We removed one particular table from the sync group as we had to do some data type changes for the columns. After removing the table from the sync group we used a script provided by Microsoft team earlier, to identify all data sync and internal tracking tables and deleted them on both the hub and member database. Then we dropped the original table as well.

After recreating the table with new schema changes, we tried to sync the table again by adding it to another existing sync group.

First time when we synced to the member DB, the table with old schema was created on the member DB. Unsure how this happened, we repeated the process of removing the table from the sync group, cleaned up the data tracking objects on both hub and member DB again.
When we are adding the table back to the sync group we are now getting error message "Failed to perform data sync operation: The data type, size, precision, or scale of column 'Deal Closing Date' has been changed and this operation is not supported." The table is not getting added to the sync group at all.

Please help us understand where is ADS finding the old schema for the table and comparing it against? We need to sync the table to the client's member DB ASAP and unable to find out where the old schema is still present. The script file used to identify sync objects is attached. Thanks!

Aaron Wong 0 Reputation points

2025-06-25T16:38:09.65+00:00
Hi Mahesh,

Also check for and remove entries in scope_config and scope_info tables manually if needed.

How to check the data for the specific table in these and delete them? Is there a script that can help us delete the data for the specific table instead of removing or touching other working table's data?

Unfortunately, removing and readding the sync group is not an option for us as our client is using the member DB tables from the sync group and cannot drop the tables from production.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-25T18:45:48.0433333+00:00

Hi Aaron Wong,

Could you please share the requested details via private message as it will help us conduct a more thorough investigation.

Thank you.
Aaron Wong 0 Reputation points

2025-06-26T07:01:53.2566667+00:00
Hi Mahesh, We were able to add the table again to the group by following the below steps:

While adding the table to the sync group, selected only 3-4 columns and unchecked the rest of the columns. The 3-4 columns picked were not changed from before

Table got added successfully. Then we did a sync to ensure no other errors occurred .

Changed the sync group configuration and selected all the remaining columns in the table and synced it. Sync completed successfully.

Not sure why it worked with few unchanged columns, and not the full table in the first place, when the table was totally removed before adding it back.

We still want to know about scope_config and scope_info tables and how to clear them if required.

Please provide any script or details in that regard which can help us troubleshoot in future. Thanks!

Mahesh Kurva 5,025 Microsoft External Staff Moderator

Hi Aaron Wong,

Glad to know that your issue was resolved.

Please provide any script or details in that regard which can help us troubleshoot in future.

This script:

Removes tracking tables

Drops sync triggers

Deletes entries from scope_config, scope_info, and scope_info_with_cleanup.

Before using the script.

Run it on both the hub and member databases.

Make sure no sync operations are running.

Replace 'Deals' with your actual table name.

Backup your databases before running.

-- Replace 'Deals' with your actual table name
DECLARE @TableName NVARCHAR(128) = 'Deals';
-- Drop tracking table
IF EXISTS (SELECT * FROM sys.tables WHERE name = @TableName + '_tracking')
BEGIN
    EXEC('DROP TABLE [' + @TableName + '_tracking]');
    PRINT 'Dropped tracking table: ' + @TableName + '_tracking';
END
-- Drop insert trigger
IF EXISTS (SELECT * FROM sys.triggers WHERE name = @TableName + '_insert_trigger')
BEGIN
    EXEC('DROP TRIGGER [' + @TableName + '_insert_trigger]');
    PRINT 'Dropped insert trigger';
END
-- Drop update trigger
IF EXISTS (SELECT * FROM sys.triggers WHERE name = @TableName + '_update_trigger')
BEGIN
    EXEC('DROP TRIGGER [' + @TableName + '_update_trigger]');
    PRINT 'Dropped update trigger';
END
-- Drop delete trigger
IF EXISTS (SELECT * FROM sys.triggers WHERE name = @TableName + '_delete_trigger')
BEGIN
    EXEC('DROP TRIGGER [' + @TableName + '_delete_trigger]');
    PRINT 'Dropped delete trigger';
END
-- Delete from scope_config
DELETE FROM scope_config WHERE scope_name LIKE '%' + @TableName + '%';
PRINT 'Deleted entries from scope_config';
-- Delete from scope_info
DELETE FROM scope_info WHERE scope_name LIKE '%' + @TableName + '%';
PRINT 'Deleted entries from scope_info';
-- Delete from scope_info_with_cleanup
DELETE FROM scope_info_with_cleanup WHERE scope_name LIKE '%' + @TableName + '%';
PRINT 'Deleted entries from scope_info_with_cleanup';

I hope this information helps. Please do let us know if you have any further queries.

Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-27T13:12:18.91+00:00

Hi Aaron Wong,

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Aaron Wong 0 Reputation points

2025-06-25T16:38:09.65+00:00

Hi Mahesh,

Also check for and remove entries in scope_config and scope_info tables manually if needed.

How to check the data for the specific table in these and delete them? Is there a script that can help us delete the data for the specific table instead of removing or touching other working table's data?

Unfortunately, removing and readding the sync group is not an option for us as our client is using the member DB tables from the sync group and cannot drop the tables from production.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-25T18:45:48.0433333+00:00

Hi Aaron Wong,

Could you please share the requested details via private message as it will help us conduct a more thorough investigation.

Thank you.
Aaron Wong 0 Reputation points

2025-06-26T07:01:53.2566667+00:00

Hi Mahesh, We were able to add the table again to the group by following the below steps:

While adding the table to the sync group, selected only 3-4 columns and unchecked the rest of the columns. The 3-4 columns picked were not changed from before

Table got added successfully. Then we did a sync to ensure no other errors occurred .

Changed the sync group configuration and selected all the remaining columns in the table and synced it. Sync completed successfully.

Not sure why it worked with few unchanged columns, and not the full table in the first place, when the table was totally removed before adding it back.

We still want to know about scope_config and scope_info tables and how to clear them if required.

Please provide any script or details in that regard which can help us troubleshoot in future. Thanks!
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-27T13:12:18.91+00:00

Hi Aaron Wong,

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Hi Aaron Wong,

Greetings!!

The issue you're encountering with Azure Data Sync likely stems from residual metadata or internal tracking information that wasn't fully cleared, even after running the cleanup script.

Here’s a recommended approach to fully reset the sync configuration for the modified table:

Remove the Table from Sync Group Again

Ensure the table is removed from the sync group in the Azure portal.

Clean Up Metadata Thoroughly

Use the Microsoft-provided script to remove all tracking tables, triggers, and metadata.
Also check for and remove entries in scope_config and scope_info tables manually if needed.

Drop and Recreate the Table

Confirm that the table is dropped and recreated with the new schema on both hub and member databases.

Clear Sync Group Cache (if possible)

Unfortunately, Azure doesn’t provide a direct way to clear sync group cache. You may need to:

Delete and recreate the sync group if the issue persists.
Or create a new sync group specifically for this table.

Add the Table to the Sync Group

Add the newly created table to the sync group and initiate sync.

Please refer the below similar thread, that might help you:
https://learn.microsoft.com/en-us/answers/questions/2282901/azure-data-sync-to-on-prem-sql-succeeded-but-the-d

If above steps are not resolved your issue, please provide requested details in Private message.

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

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2025-06-26T09:41:49.83+00:00

Hi Aaron Wong,

Glad to know that your issue was resolved and will update you regarding your ask.

Thank you.

Share via

Azure Data Sync : Error while adding a table back to sync group

1 answer

Your answer