Azure SQL Sync Group to on-premise error

0
votes

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.

1
azuresynchronizationazure-sql-database
Are there any invalid relations or indexes on the table? The datatypes shouldn't be an issue based on what you have in the screenshot. Generate the table as a (create) script including all information and see what that gives you. – rickvdbosch
Do you have a relationship established between PlayerId and a Player’s table? Do the fields involved in the relationship have the same data type? Does PlayerId of that table exist on both the hub database and the member database? Schema differences/changes have to be manually propagated. When you add a field to a table you should also add it to member databases manually. – Alberto Morillo
Data types are the same in all relationships and the same error persists when creating both hub and child databases from the same backup so they mirror exactly. I'm not sure what would be deemed an "invalid relation or index" – Ian

1 Answers

1
votes

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.