How to Fix the “Triggers on Memory-Optimized Tables Must Use WITH NATIVE_COMPILATION” Azure SQL Data Sync Error

Here’s how to quickly fix a common error that occurs while executing Azure SQL Data Sync.

Problem

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.”

Cause

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.

Solution

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.