0

I'm getting the 1: https://i.stack.imgur.com/EiSRL.png

I realize there is an earlier post about this topic with an answer that seems to have been suitable for at least 2 people so far. Azure Data Sync Clustered Index Error

However, I have tried implementing the suggestion and can't get it to accept the non-clustered index on the Azure side to sync. I have tried refreshing the schema on both sides in the Azure tables within the sync group. I tried dropping the tables and letting Azure provision them on its own. Nothing yet has worked for me. Does anyone know if this option should still work or is there something I am overlooking?

HubTableStructure

MemberTableStructure

MemberTestTable

HubTestTable

Wuf_fang
  • 21
  • 4
  • Is it possible for you to share the current table structure on both, the hub database and the member database? – Alberto Morillo Sep 06 '18 at 19:25
  • What is the data type of the clustered index? It cannot be a uniqueidentifier, for example. Some data types are not supported as clustered index by SQL Data Sync. – Alberto Morillo Sep 06 '18 at 19:46
  • I'm not sure exactly what you mean by share the current table structure. If I understand you correctly I drilled down into the tables and provided two snips of a table from the Hub and the member back up in the original question. It seems that the S/O text editor will no allow me to paste a juxtaposition or the two images so sorry about that. Please let me know if this is helpful. – Wuf_fang Sep 06 '18 at 19:47
  • Thanks for adding the table structure – Alberto Morillo Sep 06 '18 at 19:47
  • I will have to share this with Microsoft Engineers and try get some possible causes and solutions/workarounds. – Alberto Morillo Sep 06 '18 at 19:55
  • Thank you for your patience. – Alberto Morillo Sep 06 '18 at 20:01
  • Another possible cause could be a circular reference in the database (i.e. you have two tables referencing each other). Could you please verify that? – Alberto Morillo Sep 06 '18 at 20:30
  • I am not sure that circular reference is the case, however, I am looking into it. – Wuf_fang Sep 07 '18 at 20:03

1 Answers1

0

From the images you added to your question we do not see a clustered index created yet. The clustered index is a requirement to use SQL Data Sync.

enter image description here

A Microsoft engineer is asking if you recreated the table with the clustered index on the hub and on the member database. After that he suggest you need to remove the table and then add it back to get the new changes. Or recreate the sync group may be easier.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • let me give it try. . . I will get back to you! – Wuf_fang Sep 06 '18 at 21:18
  • I think the fact that there is no clustered index is the issue. I created a mock table, synced it to the hub and accidentally let it auto-provision the hub table. In this case, it actually synced but left out the foreign key relationship. I then dropped the table and both member/hub tracking tables, ran the create script of the member to create the hub table and then tried to sync. That time the sync completed with warnings and no data. I included the mock table structures in the original question. – Wuf_fang Sep 07 '18 at 20:05
  • As mentioned in my answer, the clustered index is a requirement for each table to be synced with SQL Data Sync. – Alberto Morillo Sep 07 '18 at 20:55
  • Indeed you are correct adding the clustered index is all it took. In my case, I needed to use the export wizard to generate a script with data from a source DB then add a clustered index in the script before running. Thanks for your help, it's greatly appreciated. – Wuf_fang Sep 10 '18 at 20:49