question

AnkitRathod-6794 avatar image
1 Vote"
AnkitRathod-6794 asked AlbertoMorillo commented

SQL Server Side Transaction on Azure SQL Database

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

AlbertoMorillo avatar image
1 Vote"
AlbertoMorillo answered AlbertoMorillo edited

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.

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.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

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


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.

AnkitRathod-6794 avatar image
0 Votes"
AnkitRathod-6794 answered AlbertoMorillo commented

Hi @AlbertoMorillo ,

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". ?

· 1
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.

"Elastic Distributed Transaction" are available for .NET applications using ADO .NET and requires this library.

For the "Managed Instance distributed transactions" all you need is T-SQL as explained here.

 SET XACT_ABORT ON
 BEGIN DISTRIBUTED TRANSACTION
     -- insert into local server
     INSERT INTO db01.dbo.t01 (tag, utc_time)
         VALUES ('distributed_transaction_tag', GETUTCDATE())
     -- insert into remote, linked server
     INSERT INTO [linked_server_02].db01.dbo.t01 (tag, utc_time)
         VALUES ('distributed_transaction_tag', GETUTCDATE())
 COMMIT


0 Votes 0 ·
AnkitRathod-6794 avatar image
1 Vote"
AnkitRathod-6794 answered AlbertoMorillo commented

Thanks for Clearing my doubt..

· 1
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.

You are welcome! Thank you for visiting Microsoft QA forums!

0 Votes 0 ·