I have a large(~33 millions records) on-premise SQL Server database which must be replicated to an SQL Azure database (near-realtime replication is required). I'm trying to use Attunity Replicate software to achieve this.
I created a task with Full Load option specified which successfully uploaded initial data to Azure.
After that I created another Task with Apply Changes option specified, but this task ends with errors:
Failed to execute statement: 'INSERT INTO [attrep_apply_exceptions] values ( ...'
RetCode: SQL_ERROR SqlState: 42000 NativeError: 40054 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server] Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. Line: 1 Column: -1
Attunity created [attrep_apply_exceptions] table in the Azure database which doesn't have any clustered index, so insert fails (Azure doesn't allow tables without clustered index).
Why is it happening? Should I add an index myself?
All sql-azure tables must have a clustered index. You will be able to create a table without one but once you insert your first record you will see the message:Tables without a clustered index are not supported in this version of SQL Server
There is a list of Azure Sql limitations/differences here
To answer your question, yes you must add a clustered index yourself.