Lesson Learned #432: Resolving DataSync Failures in Azure SQL Database Caused by Custom Triggers

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

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];


Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.