Cross-instance distributed transactions with Azure SQL Managed Instance

[sasapopo]

Edit (August 2022): Private preview for MS DTC support for Azure SQL Managed Instance is available.

Introduction

With Azure SQL Managed Instance, you don't need MS DTC to run distributed transactions! You can now migrate to Azure or build distributed applications with Managed Instance which natively supports cross-instance T-SQL and .NET distributed transactions. 

With recent modernization, distributed transactions are supported between Managed Instances as the only supported transaction participants. If your application runs .NET or T-SQL distributed transactions on SQL Server and of course requires MS DTC for that, you can now migrate to Azure SQL Managed Instance and distributed transactions will simply work. Also, if you need to build a multi-server environment in the cloud that needs to run queries in a transactionally consistent fashion, Managed Instance with native support for distributed transactions is a great place for your app.

Distributed transactions on Managed Instance can easily be setup with two simple steps:

1. Create Server trust group.
2. Ensure there is network connectivity between your instances.

In the rest of the blog, you will get more info on how to easily setup your Managed Instance to run distributed transactions.

Creating a Server trust group

Server trust group (STG) is an entity that manages security requirements for running distributed transactions. You can create it in Azure Portal, or via Azure PowerShell  or Azure CLI.

Here is an example of Azure PowerShell script that creates STG for two Managed Instances.

Login-AzAccount
Select-AzSubscription -SubscriptionId "c829c89a-0931-4310-a49c-fa8f42a8cff2"
$managedInstanceList = @()
$managedInstanceList += Get-AzSqlInstance -Name "sqlmi1" -ResourceGroupName "rg1"
$managedInstanceList += Get-AzSqlInstance -Name "sqlmi2" -ResourceGroupName "rg1"
New-AzSqlServerTrustGroup -ResourceGroupName "rg1" -Location "West Europe" -Name "stg1" -GroupMember $managedInstanceList -TrustScope "GlobalTransactions"

Network connectivity

For distributed transactions to work, there must be a network connectivity between Managed Instances. If instances are on different virtual networks, VNET peering needs to be setup. 
Additionally, port 5024 and port range 11000-12000 need to be allowed for outbound and inbound VNET traffic in the Network Security Groups assigned to Subnets that host Managed Instances.

Note: when Server trust group is not created, or there is no network connectivity between instances, distributed transaction will fail with following error message.

Msg 8510, Level 20, State 3, Line 14
Enlist operation failed: Global Transaction failed. SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.

Distributed Transaction examples

With STG and network settings in place, you can run distributed transactions across Managed Instances with linked servers.

Here's an example with two SELECT queries against master database.

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
	-- select from local server
	SELECT * FROM master.sys.databases
	-- select from remote, linked server
	SELECT * FROM [linked_server_02].master.sys.databases
COMMIT

Here is another example with INSERT queries against user databases.

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

Additional examples with .NET TransactionScope are available in the documentation.