Search code examples
azureazure-sql-databasedatabase-replicationclustered-indexazure-data-sync

Azure Data Sync Clustered Index Error

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 ?

Solution

  • 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