Azure SQL Database provides a robust DataSync service to synchronize data across multiple Azure SQL databases or between on-premises SQL Server and Azure SQL databases. While generally reliable, some exceptions can disrupt the smooth flow of data synchronization. One such error occurs when custom-defined triggers interfere with DataSync's internal processes, resulting in a failure like the one described below: Sync failed with the exception 'An unexpected error occurred when applying batch file sync_XXX\\XX-XXX-XYZ-afb1-XXXX.batch. See the inner exception for more details.Inner exception: Index was outside the bounds of the array. For more information, provide tracing ID ‘NNNN-3414-XYZ-ZZZ-NNNNNNNX’ to customer support.'
Analyzed the logs, we found that the error message points to a failure when applying a batch file for data synchronization, with an inner exception indicating that an "Index was outside the bounds of the array." In this situation the error occurs when a custom trigger modifies the underlying data in a way that interferes with DataSync's internal "data sync trigger" responsible for bulk-insert operations.
In this situation, once we have identified the trigger and table that is causing this issue, we temporarily disable the identified custom triggers and attempt to synchronize the data again. If the data syncs successfully, this confirms that the custom trigger is causing the issue.
-- Disable Trigger
DISABLE TRIGGER [Trigger_Name] ON [Table1];
-- Enable Trigger
ENABLE TRIGGER [Trigger_Name] ON [Table1];
