Sync failed due to sync group out of date although it was working fine same day

Question

Sync failed due to sync group appear out of date with no reason. It was running perfectly until this morning then stopped all of sudden.

This issue appeared today although there was no previous errors or warnings.

Deleting and re-creating the sync group and start sync all over from the start is time consuming and a real headache. I think this is a bug in the azure system.

Answer 1

Hi George Raphael •,

Thanks for your question and Sorry to hear you are facing this issue

As per the Microsoft document https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-troubleshoot?view=azuresql#setup-date2 the cause of a sync group has an "Out-of-Date" status

If one or more changes fail to apply for the whole retention period of 45 days, a sync group can become outdated.

And to resolution is to avoid an Out-of-Date status for a sync group, examine the results of your sync jobs in the history viewer on a regular basis. Investigate and resolve any changes that fail to apply.

If a sync group's status is Out-of-Date, delete the sync group, and then re-create it.

Thank you!

Answer 2

I @George Raphael Adding to Rahul Randive here is the action plan that we should follow if you do not want to drop the Sync Group.

Please be careful with the steps, they may result in data loss if not followed correctly.

 1) Stop Auto-sync on the Sync Group.

2. Go to the Sync Metadata database to find the scope name for the Sync Member under the Sync Group by running -

select name, scopename,id, * from [dss].[syncgroupmember] where name = '<sync_member_name>'

It shall return only 1 row. The id should be a GUID value.

3. Go to the Hub database, and find the scope_id on the Sync Member from #2 by running -

select * from [DataSync].[scope_info_dss] where sync_scope_name = '<scopename from #2>'

4. If there is one entry, run the following query to clean up the Tombstone-knowledge -

begin tran

update [DataSync].[scope_info_dss] set [scope_tombstone_cleanup_knowledge] = null where

sync_scope_name = '<scopename from #2>'

If there is only 1 row change -

commit tran

5. Go to Sync Metadata database to change the memberstate to 6 -

update [dss].[syncgroupmember] set memberstate = 6 where name = '<sync_member_name>'

6. Trigger a Sync.

 

After the Sync, we need to check that the data is correct. you can use table-diff tools, or SSDT - https://docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/ssdt/how-to-compare-and-synchronize-the-data-of-two-databases?view=sql-server-ver15 to compare the data. you can also use other data comparison tools. (Like, Red-gate - https://www.red-gate.com/products/sql-development/sql-compare/)

I hope this information helps please let me know if you have any additional questions.

Regards

Geetha