Azure Data Sync Clustered Index Error

0
votes

We are trying to setup an Azure Database Sync group to replicate our data from an on-premise server to an Azure SQL database. This as a first step for a migration to Azure.

The Sync Group and Sync Agent have all been set up. When we press the 'Sync' button we receive following error:

Trigger Sync Failed: Failed to perform data sync operation: Table '[dbo].[DocumentTypeDocumentVariables]' do not have clustered index.

enter image description here

This table did not have a clustered index but an unclustered unique primairy key index.

  • We tried to add an clustered index but this give the same error.
  • We tried to remove the unclustered PK index and created a unique clustered PK index but this gives us the same error.

enter image description here

What could be wrong ?

1
azureazure-sql-databasedatabase-replicationclustered-indexnon-clustered-index
I read in the source "docs.microsoft.com/en-us/azure/sql-database/…" following limitations A table cannot have an identity column that is not the primary key. The names of objects (databases, tables, and columns) cannot contain the printable characters period (.), left square bracket ([), or right square bracket (]). Azure Active Directory authentication is not supported. This table does not have a PK with an identity. maybe that is the problem. – Fluffymittens
When you remove the unclustered PK index and created a unique clustered PK index, did you retry the steps from scratch? I mean recreate the group and agent, and then try sync again. I ever met the same error before and it worked after I changed the index to clustered index, and retry from the scrach. Seems the Data Sync feature now only supports databases which have clustered PK index, but the official doc says "Each table must have a primary key" instead of "must have a clustered PK". – forester123

1 Answers

2
votes

Please recreate the database on Azure SQL Database with the same schema (including indexes) as the on-premise database and with no data, then try to configure SQL Data Sync. You can create a script with no data of the on-premise database using SQL Server Management Studio and then use the script to recreate all database objects on Azure SQL Database. Having no data on Azure will make the initial sync faster also. If both database schemas have differences then SQL Data Sync won't work