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

Posted in: Azure, Microsoft SQL Server, Technical Track
azure sql data sync

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.

 

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Lead Microsoft Data Platform Consultant
Pio Balistoy is a Microsoft MVP for Data Platform from Singapore. He has been a Database professional for more than 17 years. He brings his passion for SQL to the community by being one of the Community leads for both Philippine Data Platform Forums (formerly Philippine SQL Server User Group) and Singapore SQL PASS.

No comments

Leave a Reply

Your email address will not be published.