azuresynchronizationazure-sql-databaseazure-data-sync

Azure SQL Sync Group to on-premise error


The Sync Group is currently in preview and I've setup a sync group for an Azure sql database to sync one-way to an on-premise sql 2014 instance.

The setup process went well, including installing the local sync agent on our server. No errors.

The issue comes to selecting the tables to sync. Selecting all of them was an instant failure, as when I clicked Sync, I was given the error:

enter image description here

So, I selected only one table, when was successful. I repeated this, one by one, ensuring xml columns were de-selected as these are not supported, right up until the last one, the Results table, which fails.

Any idea why this table would cause the error?

enter image description here

UPDATE

I've pinpointed the issue to the Results PlayerId. When I de-select this field, no error is shown when I start the sync (but this won't work as it'd a FK that can't be null). Data types are the same in primary key and foreign key.


Solution

  • A Microsoft engineer examined the trace on our side, and found the issue occurred when you try to trigger a sync. The actual error message that appears is:

    There is a circular dependency between [dbo].[StrokeplayRoundResults] and [dbo].[Results].

    At this time, SQL Data Sync does not support circular dependencies. This behavior is by design. The reason behind this is SQL Data Sync is trying to find the right order to inserting data that does not violate foreign key constraint. If there are circular dependencies, such order does not exist.

    Microsoft will be showing a more explicit error message very soon, instead of “An unexpected error occurred while processing the request”.

    Hope this helps. Thank you for your patience.