영업: 1-800-867-1380

 none
Data Sync between data centers: performance issue

    질문

  • We are trying to set up a Data Sync between two databases, one located in West Europe, one located in East Asia.

    The sync is uni-directional, from the Hub database. The hub is located in West Europe

    The size of the database is about 6 GB, with the bulk of it being a table containing binary data (~12000 rows, ~5.5GB)

    The first sync was completed after 136 hours: "162006 rows have been processed in 491217 seconds."

    After that, the "Last Synched" status of the sync group still shows "N/A", and additional syncs fail.

    Syncs run only 2 to 3 times a day, while the schedule is set to 30 minutes

    Is this an expected duration for a one-way sync of databases this size?

    Additional information:

    - Server ID: 3a352690-e999-44aa-bd1c-16f0e43182ce

    - Sync group ID: deeb7603-9b36-487c-af8f-7735e480e250

    - Region: West Europe (hub) and East Asia

    - Timeframe: the last 2 weeks

    - Tracing ID's of provisioning and the first sync: f7513fd0-e7ee-432f-9162-f970b8221a18, 16af6f35-aa30-4726-8661-53610784d6a7, a199633d-7545-4fa5-9fdd-86d5db78fd59

    - tracing ID's of some additional failed syncs: 5c81985f-eb60-4f0f-8eba-d3278981f5d1, 6e3e74f3-7605-4446-a201-6d3fe51012b9, 269b3ea5-944b-47e9-93e9-ebc54dc242ad

    2012년 4월 16일 월요일 오전 10:54

답변

  • when doing an initial sync, make sure only one member has a copy of the database. if you did an export/import, your West Europe has a copy of the data, then the same data is imported to your East Asia DB. however, DataSync has no idea that these data are one and the same since the synchronization knowledge of what has been synched is only populated the first time on first sync.

    you have a long initial sync because you run into conflicts. if West Europe is the hub, when you first synched, even though your East Asia has the data already, the data from West Europe was actually downloaded and sync tried to apply them. the application would fail since the rows already exists (PK violations on insert). Data Sync now has to resolve them one by one. thus you have a very long initial sync.

    • 답변으로 표시됨 Mel Gerats 2012년 4월 23일 월요일 오전 9:52
    2012년 4월 20일 금요일 오전 2:30
    답변자

모든 응답

  • @Mel

    Please test you internet connection speed when you face the issue using speed test site , so you can verify the bandwidth and confirm whether the issue is in your end or in Azure end.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    2012년 4월 17일 화요일 오전 6:18
  • 40Mbps download, 5,4Mbps upload.

    However, I fail to see how this affects data transfer between Windows Azure datacenters

    2012년 4월 17일 화요일 오전 8:30
  • It is expected that the inital sync for DSS would take long time if the tables' size involving in Sync Groups are large. We are improving the initial sync's performance at incoming SUs.


    Oliver Yao - MSFT

    2012년 4월 17일 화요일 오후 2:34
  • Would it be possible to give some information about the subsequent failed syncs?

    Both databases are equal in schema and data, the one in East Asia was created via export/import of the one in West Europe.

    Could the failures be due to timeouts?


    2012년 4월 18일 수요일 오전 7:43
  • when doing an initial sync, make sure only one member has a copy of the database. if you did an export/import, your West Europe has a copy of the data, then the same data is imported to your East Asia DB. however, DataSync has no idea that these data are one and the same since the synchronization knowledge of what has been synched is only populated the first time on first sync.

    you have a long initial sync because you run into conflicts. if West Europe is the hub, when you first synched, even though your East Asia has the data already, the data from West Europe was actually downloaded and sync tried to apply them. the application would fail since the rows already exists (PK violations on insert). Data Sync now has to resolve them one by one. thus you have a very long initial sync.

    • 답변으로 표시됨 Mel Gerats 2012년 4월 23일 월요일 오전 9:52
    2012년 4월 20일 금요일 오전 2:30
    답변자
  • Thanks June, this is very useful information. I'll try initiating a sync with only data in the hub database. 
    2012년 4월 20일 금요일 오전 9:26
  • I successfully set up a new data sync to an empty database. This actually worked great! The initial sync still took about 20 hours, but after that there are several successful synchronisations in the log, both with and without changes. 

    However, after about ten successful syncs the errors started popping up, and I keep getting:

    Sync failed with the exception "Sync was aborted because more than 1000 changes failed to apply. Examine your table schemas to look for conflicting constraints or incompatible data types that may prevent sync from succeeding."

    For more information, provide tracing id ‘66677805-41f3-4c65-9516-71e766dd2b62’ to customer support.

    So while it solved the excessive duration problem, I'm now facing another one. Any thoughts on why it first worked and then started failing?

    2012년 4월 23일 월요일 오전 9:58
  • check for constraints (FKs, check constraints, unique index, etc...) on the destination database...
    2012년 4월 23일 월요일 오전 11:24
    답변자
  • Both databases were migrated from the same database with the SQL Azure Migration Wizard, one schema only, one schema + data. To verify I compared the schema again, they are identical except for the objects created by the DataSync.

    2012년 4월 23일 월요일 오후 12:38
  • so it was successfully synching and just started failing?

    have you done any schema changes? its displaying more than 1000 failed rows, do you have that many changes?

    2012년 4월 23일 월요일 오후 12:48
    답변자
  • so it was successfully synching and just started failing?

    have you done any schema changes? its displaying more than 1000 failed rows, do you have that many changes?

    Indeed, the schema has not changed, it was synchronising successfully and then started failing. More than 1000 changes is certainly realistic, in the log is a successful entry with more than 1000 changes .

    The log looks more or less like this (chronologically)

    Database provisioned successfully in 270 seconds

    Sync completed successfully in 73139 seconds (546329 changes)

    Sync completed successfully in 66 seconds (660 changes)

    Sync completed successfully in 7 seconds (0 changes)

    ... (about 10 times)

    Sync completed successfully in 7 seconds (0 changes)

    Sync completed successfully in 224 seconds (32703 changes)

    Sync failed with the exception "Sync was aborted because more than 1000 changes failed to apply." ...

    ... (about 30 times now)


    • 편집됨 Mel Gerats 2012년 4월 23일 월요일 오후 1:24 formatting
    2012년 4월 23일 월요일 오후 1:08
  • Hey Mel Gerats,

    I took some time to look into the detailed logging in OpStore, it shows the same error message due to too many conflicts happening when DSS applies the changes to the remote db side. do your tables have circlular FK references? or self-referencing? DSS currently does no support circular reference. Self-referencing is not fully supported. That could be the reason for your case.


    Oliver Yao - MSFT

    2012년 4월 24일 화요일 오전 9:22
  • Hi Olivier, thanks for looking into this.

    Some of the tables are indeed self-referencing, but I don't think there are circular references. 


    2012년 4월 25일 수요일 오전 10:13
  • Hi Oliver,

    According to this blog post: http://blogs.msdn.com/b/windowsazure/archive/2012/01/26/announcing-sql-azure-data-sync-preview-refresh.aspx, synchronization of self-referencing tables is now supported.

    have this been taken out? because looking at the Jan 2012 release note (http://msdn.microsoft.com/en-us/library/hh456372.aspx), there is no mention of it anymore.

    JuneT

    2012년 4월 25일 수요일 오전 11:54
    답변자
  • Hey JuneT,

    It is not taken out.

    During the synchronization, DSS orderes rows by the PK, and it is possible that the PK of the child row is ordered before the parent row. DSS does retry failed rows, however this can lead to a long sync time, and possibly failing with errors. The change can be completely synced to remote side if number of conflict errors are not hit over 1000 above. That is the reason I was saying not fully supported for now. There has one limitation there.

    Hey Mel,

    you could remove the FK constraint from your remote db as workaround for now if this does not impact to your needs.


    Oliver Yao - MSFT

    2012년 4월 26일 목요일 오전 6:46