Share via

SQL Server Side Transaction on Azure SQL Database

Ankit Rathod 391 Reputation points
2021-04-30T19:59:05.307+00:00

I currently have an application that is hosted on their on-premises environment. The application is currently connecting to two databases on the on-premises environment. The databases are named DB1 and DB2.

I have to move the databases onto Azure. The databases have to support server-side transactions across both of the databases.

Will it be supported in Azure SQL DB ?

OR

Only Azure SQL Managed Instance Supports it ?

Azure SQL Database
0 comments
Accepted answer
  1. Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator
    2021-05-01T22:57:08.18+00:00

    My understanding, you basically have 2 options to achieve the same thing. For current applications using MSDTC you have these 2 options to migrate them to the Azure cloud, and one option does not exclude the other.

    For using the elastic distributed transactions you need to meet 3 specific requirements: .NET application, ADO. NET and the library.

    If your application does not meet all those options you have Managed instance distributed transactions using T-SQL. You application does not need to meet any requirements, you only need to move the database to an Azure Managed Instance.

    2 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ankit Rathod 391 Reputation points
    2021-05-02T05:54:50.933+00:00

    Thanks for Clearing my doubt..

    1 person found this answer helpful.
  2. Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator
    2021-04-30T20:46:19.083+00:00

    A server-side distributed transactions using Transact-SQL are available only for Azure SQL Managed Instance. Distributed transaction can be executed only between Managed Instances that belong to the same Server trust group. In this scenario, Managed Instances need to use linked server to reference each other.

    Source: Microsoft Learn.

    0 comments
  3. Ankit Rathod 391 Reputation points
    2021-05-01T10:19:10.573+00:00

    Hi @Alberto Morillo ,

    Thanks for your response but im just getting Little confused in below statement., from URL - Managed Instance distributed transactions are available in Transact-SQL.

    Elastic database transactions for Azure SQL Database and Azure SQL Managed Instance allow you to run transactions that span several databases. Elastic database transactions are available for .NET applications using ADO.NET and integrate with the familiar programming experience using the System.Transaction classes. To get the library, see .NET Framework 4.6.1 (Web Installer). Additionally, for Managed Instance distributed transactions are available in Transact-SQL.

    What is Difference between "Elastic Distributed Transaction supported in Azure SQL DB" and "Managed Instance distributed transactions are available in Transact-SQL". ?

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.