Use Autonomous Transaction in Azure SQL Database

Question

Hello,

I need to replicate Oracle's autonomous transaction in Azure SQL Database.
I know I can use CLRs in SQL On-premise and Azure SQL Database Managed Instance, but I have a requirement to work on Azure SQL Database and CLRs are not supported in this environment.

How can I replicate this functionality?

Thanks,

G.

Answer 1

Hi @G Cocci , welcome to Microsoft Q&A forum. Unfortunately CLR and autonomous transactions are not supported on Azure Sql Databases.

Autonomous Transaction in On-premise Sql Server and Azure Sql Database: : As you correctly mentioned, autonomous transactions are not directly supported in both Sql Server and Azure Sql Database. In Sql Server, we can still use linked server objects (sp_addlinkedserver) to achieve the same. However, linked server is not available on to Azure SQL database service yet. One way is to use elastic queries but they also come with limitations like we cannot perform DML operations through them.

CLR support in Azure Sql Database: CLR integration with SQL Server is still not supported in Azure Sql Database, however it can be used on Azure Sql managed Instance.

Alternative approach is to either use an Azure VM and install the Sql Server on the VM, or use the Azure Sql Managed Instance.

Below are some links that talk about the similar discussion if you want to refer:

1. https://stackoverflow.com/questions/37342550/does-or-does-not-sql-azure-support-clr-assemblies
2. https://social.msdn.microsoft.com/Forums/vstudio/en-US/ed7e9759-7188-46a6-90f5-a3f0cd4c2c78/azure-sql-server-8211-linked-server-autonomous-transaction-logging-workaround

Please let us know if this helps or we can discuss further in case you have more queries.

----------

If this helps the query, please 'Accept Answer' as this could help other community members facing similar issues.