Here’s how to quickly fix a common error that occurs while executing Azure SQL Data Sync.
You’re starting a data sync from an Azure SQL Database and the database provisioning step fails with the following error:
Database provisioning failed with the exception “Triggers on memory-optimized tables must use WITH NATIVE_COMPILATION.Inner exception: SqlException ID: <GUID Exception ID>, Error Code: -2146232060 – SqlError Number:10777, Message: SQL error with code 10777 For more information,
provide tracing ID ‘<Trace ID’ to customer support.”
Data Sync doesn’t support the memory-optimized table. As part of database provisioning, Data Sync creates triggers for dml to tables chosen for synching. The error returned is from Data Sync failing to create the triggers. Triggers for memory-optimized tables need to be one atomic block and have the Native_Compilation and Schemabinding properties. The triggers that Data Sync are trying to create don’t have these options.
Deselect the memory-optimized tables from the tables you have chosen to sync. You can quickly identify the memory-optimized tables using the query below:
SELECT SCHEMA_NAME(Schema_id) as [Schema], name as [Table] FROM sys.tables where is_memory_optimized = 1 GO
Once you have identified the tables, deselect them from the sync and save the changes. Restart the sync and the error should go away. For more information on Azure SQL Data Sync limitations, you can check out What is SQL Data Sync for Azure? – Azure SQL Database | Microsoft Docs
Note: If you reached this page because you were genuinely receiving the error while creating a trigger on a memory optimized table, the solution would be to create the trigger with the options Native_Compilation and SchemaBinding included in your create script. Make sure that the trigger body is composed of a single atomic block of code. For more information on creating a trigger/procedures on memory-optimized tables, refer to this document.
I hope you found this post useful. Feel free to drop questions in the comments, and make sure to sign up so you don’t miss the next post.