Azure data sync taking very long to process.

Question

Azure data sync taking very long to process.

Aparna Manoharan 40

Azure data sync taking very long to process. It takes about 3.5 hrs to process 47k. We are not expecting these many records in each sync run . This lag is happening suddenly. How do we know what is causing this lag and We have more than 100 tables in syn group, Is there way to find out which table records were processed in each sync run?

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-10T17:40:25.0033333+00:00

Hi @Aparna Manoharan,

Just checking in to see if the below answer provided by @Sina Salam 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.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-12T16:11:43.2766667+00:00
Hi @Aparna Manoharan,

Thanks for the question and using MS Q&A platform.

Each table in a sync group must have a primary key. SQL Data Sync can't sync a table that doesn't have a primary key. This may be the reason for lag.

Before using SQL Data Sync in production, test initial and ongoing sync performance.

Empty tables provide the best performance at initialization time. If the target table is empty, Data Sync uses bulk insert to load the data. Otherwise, Data Sync does a row-by-row comparison and insertion to check for conflicts. If performance is not a concern, however, you can set up sync between tables that already contain data.

For your reference, I am attaching document: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-best-practices?view=azuresql&source=recommendations#table-considerations-and-constraints.

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-13T16:34:28.1933333+00:00

Hi @Aparna Manoharan,

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.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-16T08:20:34.0966667+00:00

Hi @Aparna Manoharan,

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.
Aparna Manoharan 40 Reputation points

2024-09-16T09:39:51.4566667+00:00

Hi @Anonymous

No , I have not got the correct the solution to my query .

Could you please help to find out how to get Sync Log to check the table records that were processed in each sync run in Azure Data Sync.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-16T15:21:42.9866667+00:00
Hi @Aparna Manoharan,

Access Sync Logs:

Open the Azure portal.

Go to your SQL Database.

Select the option to sync with other databases under Data Management.

Choose your sync group.

2.View Sync Logs:

In the sync group, look for the “Sync Logs” tab.

This tab shows detailed logs of each sync operation, including the tables and records processed.

3.Detailed Logs:

For more detailed logs, you might need to enable diagnostic settings.

Go to the “Diagnostic settings” in your SQL Database.

Configure it to send logs to a Log Analytics workspace.

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-17T17:22:42.4466667+00:00

Hi @Aparna Manoharan,

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.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-18T18:28:47.1733333+00:00

Hi @Aparna Manoharan,

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.

1 answer

Your answer

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-10T17:40:25.0033333+00:00

Hi @Aparna Manoharan,

Just checking in to see if the below answer provided by @Sina Salam 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.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-12T16:11:43.2766667+00:00

Hi @Aparna Manoharan,

Thanks for the question and using MS Q&A platform.

Each table in a sync group must have a primary key. SQL Data Sync can't sync a table that doesn't have a primary key. This may be the reason for lag.

Before using SQL Data Sync in production, test initial and ongoing sync performance.

Empty tables provide the best performance at initialization time. If the target table is empty, Data Sync uses bulk insert to load the data. Otherwise, Data Sync does a row-by-row comparison and insertion to check for conflicts. If performance is not a concern, however, you can set up sync between tables that already contain data.

For your reference, I am attaching document: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-best-practices?view=azuresql&source=recommendations#table-considerations-and-constraints.

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-13T16:34:28.1933333+00:00

Hi @Aparna Manoharan,

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.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-16T08:20:34.0966667+00:00

Hi @Aparna Manoharan,

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.
Aparna Manoharan 40 Reputation points

2024-09-16T09:39:51.4566667+00:00

Hi @Anonymous

No , I have not got the correct the solution to my query .

Could you please help to find out how to get Sync Log to check the table records that were processed in each sync run in Azure Data Sync.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-16T15:21:42.9866667+00:00

Hi @Aparna Manoharan,

Access Sync Logs:

Open the Azure portal.

Go to your SQL Database.

Select the option to sync with other databases under Data Management.

Choose your sync group.

2.View Sync Logs:

In the sync group, look for the “Sync Logs” tab.

This tab shows detailed logs of each sync operation, including the tables and records processed.

3.Detailed Logs:

For more detailed logs, you might need to enable diagnostic settings.

Go to the “Diagnostic settings” in your SQL Database.

Configure it to send logs to a Log Analytics workspace.

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-17T17:22:42.4466667+00:00

Hi @Aparna Manoharan,

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.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-18T18:28:47.1733333+00:00

Hi @Aparna Manoharan,

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.

Answer 1

Hello Aparna Manoharan,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having issues with Azure data sync taking very long to process.

The reason might be varied depends on sync strategy but based on your question:

Is there way to find out which table records were processed in each sync run?

You can troubleshoot the lag in your Azure Data Sync by firstly identify if transaction volume is not too high and compute Tier is enough for the sync workload. https://learn.microsoft.com/en-us/azure/mysql/how-to-troubleshoot-replication-latency

Then, you can use Sync Log to check the table records that were processed in each sync run in Azure Data Sync. or use PowerShell or SQL query to be certain of what is causing the issues. https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-data-sync-resolve-slow-initial-sync-especially-on-two-way/ba-p/368989 and https://techcommunity.microsoft.com/t5/azure-database-for-mysql-blog/how-to-isolate-and-troubleshoot-synchronize-latency-issue/ba-p/1327004

Also, this is a similar answer on this platform for your review: https://learn.microsoft.com/en-us/answers/questions/1026950/azure-data-sync-lag-issues

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-11T17:11:11.71+00:00

Hi @Aparna Manoharan,

Just checking in to see if the above answer provided by @Sina Salam 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.
Aparna Manoharan 40 Reputation points

2024-09-12T08:56:23.6333333+00:00

@Sina Salam , Thanks for your response.

But I still did not find a solution on how to use Sync Log to check the table records that were processed in each sync run in Azure Data Sync.

Share via

Azure data sync taking very long to process.

1 answer

Your answer