none
Slow synchronisation of member database RRS feed

  • Question

  • I am managing the following setup:

    I have a SQL Server database that runs on a local server and manages our business critical data (1).
    I have a SQL Server sync setup in Azure that collects data from (1) and synchronises it to a member database.  This is managed via a hub.

    The other day I noticed that one table in my database had stopped synchronising last month.  All other tables were still synchronising.  I checked the setup and the offending table was set for synchronisation on all three (local, hub, member) databases.  There were no errors being reported in the sync log.

    I unchecked sync for that table in the hub database and allowed that setting to propogate.  Once this was done I set it to sync again.

    This had the unexpected result that the hub database performed a massive sync operation that appeared to include all tables on the database.
    It took almost 2 days to resolve itself and return to the "Good" status. 

    So now the sync is working again, including the original offending table.  The problem is that synchronisation to the member database from the hub is taking ~600 seconds.  By comparison, synchronisation from the local database to the hub is taking only 5 seconds.  The number of changes in each sync is only around 200 rows.

    Now, the sync from the hub to the member database was taking a long time before any of this - but only around 20 seconds.  It was on my task list to find out the reason why.

    Does someone have troubleshooting suggestions? These will be very welcome.

    Thursday, October 24, 2019 10:36 AM

All replies

  • Hi,

    Can you detail which tools you are currently using to investigate this issue? If you haven't already, please take a look at the Monitor and Troubleshoot information.

    The most effective step would be to Create an Azure Monitor view for monitoring to quickly look at the sync group and see what errors are being generated. If you use the failure-only filtering, the cost of this solution is free (if < 500Mb/day of ingestion).

    There is also the Troubleshoot issues with SQL Data Sync where performance degradation is discussed. This is usually do to a sync loop and circular references with multiple sync groups configured. 

    Please provide any additional information from your investigation.

    Regards,

    Mike

    Friday, October 25, 2019 12:12 AM
    Moderator
  • Thank you for your reply Mike.

    I tried to take your advice to create an Azure Monitor view.  Unfortunately the documentation that you linked to is quite old and no longer reflects reality in terms of the Azure interface.  Still, I managed to find my way through it and got to the point where I am supposed to upload a sample file called "DataSyncLog.omsview".  Apparently this file is supposed to be provided but the Azure interface no longer offers it.  A quick google search put me in touch with the necessary file but when I tried to upload it an error was displayed.

    I have now discovered that there is an ongoing issue raised on Github for it:

    https://github.com/MicrosoftDocs/azure-docs/issues/32134

    If I go to the SyncGroup page on the Azure portal there are no errors reported.  All synchronisation events are being reported as "Success".  But the sync to the member database is taking in excess of 600 seconds for less than 300 rows changed.  The associated sync from the local database to the hub takes only a couple of seconds.

    I ran the DataSyncHealthChecker in a powershell and it processed without any errors or warnings generated.

    Now, with respect to your advice about performance degradation, I checked that link and it mainly talks about sync loops between one sync group and another.  That is definitely not my problem, since I only have one sync group.

    Friday, October 25, 2019 1:24 PM
  • Thank you for this feedback. I have escalated the Azure Monitor View issue internally. As for the performance issue, can you please open an Azure Support Request, or send me your Azure Subscription ID, if you do not have an Azure Support Plan, to AzCommunity.  I can return instructions to have a support request created and the performance issue investigated.

    Things to consider are the service tiers each Azure SQL Database is currently provisioned. Are the hub and member databases provisioned at the same service tier or are they differant? Are there performance recommendations that should be implemented for the member database?

    Regards,

    Mike

    Friday, October 25, 2019 11:57 PM
    Moderator
  • Please let me know if you have pursued the option to engage Azure Support, as the support request ID will be helpful in engaging the product group further with this issue.

    If you found a solution to your issue that you could share, this would will also be helpful so others can evaluate your contribution as an applicable approach to resolving their data sync performance issue.

    Thank you,

    Mike

    Tuesday, November 5, 2019 3:30 AM
    Moderator
  • Thank you once again for your replies Mike.  I have been off this problem for a while due to other priorities, but now I am looking at it once again.

    Regarding the service tiers, yes the hub and both members DBs are using the same pricing tier:
    Standard S0: 10 DTUs

    I checked performance recommendations and there was only one - to install an index on a particular table column due to an analysis of historical queries.  This cannot be the issue, but I have accepted the recommendation and installed it anyway.

    I tried to raise a support request but it transpires that our current plan (free) does not including support for technical requests.
    I think we should upgrade to paid support but I need to consider the options here.

    Is it still useful for me to send my subscription ID? In the meantime I will try to setup an appropriate support plan.

    Sunday, December 1, 2019 9:40 AM
  • Mike: I have now upgraded our support plan and I have been able to launch a support request.  The ID is 119120122000101.
    Sunday, December 1, 2019 11:04 AM
  • Thank you for this information. I see the request. There is a recommendation to run the DataSyncHealthChecker but please indicate that you have already done this, and unless there is a version that support can offer that has additional diagnostics, you have attempted to resolve this though all available resources.

    Regards,

    Mike

    Wednesday, December 4, 2019 12:38 AM
    Moderator