question

GCocci avatar image
0 Votes"
GCocci asked Mike-Ubezzi commented

Use Autonomous Transaction in Azure SQL Database

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.

azure-sql-databaseazure-migrate
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered Mike-Ubezzi commented

Hi @GCocci, 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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @AnuragSharma-MSFT for the suggestion, I've tried using elastic query with sp_execute_remote command and it has worked!

0 Votes 0 ·

Thank you for the update and glad your solution is working. Please do not hesitate to request assistance from the Microsoft Q&A forum community if you are seeking a resolution in the future. Regards, Mike.

0 Votes 0 ·