Azure data sync is not working and not giving any error also

Question

Hi Team,

We have Azure SQL data sync which is working for last 6.5 years. But last week it suddenly stopped working we did not make any changes to Hub or member databases, neither change any SQL username or password, no updates to data sync, it was syncing the data till 27th July 2024 and suddenly stopped working on 28th July 2024. We get log messages as 'Tombstone cleanup completed successfully. xx rows deleted.' but no data is being synced between two databases, not showing any error message also. it's even not showing older logs like we would get the 'Load more' link button at the bottom to see old logs but now it's not showing this also.

for security purpose I have hidden the name.

The support team suggesting to recreate the sync group, but without knowing the root cause of the issue I don't feel I should recreate it, because it is production database and downstream systems depends on this. Kindly help

Answer 1

Hi Abhilab Das (REVEL INC) •

Welcome to Microsoft Q&A forum.

As I understand, Azure SQL Data Sync is failing for you and logs have no hint.

You can check below:

1. Changes in Table Structure: If a change has been made to the structure of the table, such as modifying the data type of a column or changing its name, synchronization may fail if the column definition no longer matches between the synchronized databases.
2. Incorrect ColumnMapping: Synchronization between databases uses column mappings to ensure proper data correspondence. If the structure of a table is changed and the column mapping is not updated accordingly.

Refer to Troubleshooting guide for Data Sync and Sync Agent here:

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-troubleshoot?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-agent-overview?view=azuresql#agent-tshoot

Unfortunately, Data Sync issues beyond the troubleshooting guide cannot be triaged remotely.

I would recommend you reach out to Azure Support so that an engineer can work with you and determine the exact problem.

Thanks

Answer 2

I have seen a sync group to stopped working when there are conflicts not resolved for more than 45 days or when other conditions occur that based on the information you provided, I consider not possible on the scenario you described. When those conflicts are not resolved the sync group may enter to "Out-of-Date" status. In this case, removing the database of the sync group and add it back can solve the issue.

Other things you can try:

1. Can you verify if the Client Agent is online. If it is not online, please put it back online. Make sure the computer where the agent is installed is not running out of free space.
2. In the Windows Services applet (Services.msc) verify SQL Sata Sync service is running.
3. i know you said no errors collected by IT engineers on your side, but could you examine Windows Event Log where the member database is installed, to see if you can find any errors related to SQL Data Sync.

Answer 3

Hi Abhilab Das (REVEL INC) •,

Glad to know that you have overcome the issue by recreation of sync group.

As I understand, you want to set up alert for Azure SQL Data Sync.

Please refer to the blogpost here https://azure.microsoft.com/es-es/blog/monitoring-azure-sql-data-sync-using-oms-log-analytics/ where you can make use of

• PowerShell Runbook to feed Data Sync Log Data to OMS
• OMS Log Analytics alert for email notifications
• OMS view for monitoring

to set up your custom Data Sync OMS monitoring solution.

Hope this helps.

Thanks