Distributed transactions available now for Azure SQL Managed Instance

[sasapopo]

[Edit] It's Nov 17th, 2022, and we've announced public preview of DTC for Azure SQL Managed Instance. Learn how you can use DTC today.

Introduction

Azure SQL Managed Instance distributed transactions are generally available! You can now run .NET and T-SQL distributed transactions across multiple Managed Instances. We support distributed transactions across Managed Instances in different regions, subscriptions and VNETs.

T-SQL and .NET distributed transactions across multiple Managed Instances

Preparation

To setup this functionality you need put all Managed Instances into Server trust group. This can be done in Azure Portal or via Azure PowerShell or CLI.

With Azure PowerShell you can create new Server trust group with New-AzSqlServerTrustGroup. Here is an example of that.

$managedInstanceList = @()

# Select instances from the first subscription.
Select-AzSubscription 54afce91-1f05-4c0a-bb97-8954574a94e0
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/54afce91-1f05-4c0a-bb97-8954574a94e0/resourceGroups/rg1/providers/Microsoft.Sql/managedInstances/sqlmi01"
$managedInstanceList += $mi
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/54afce91-1f05-4c0a-bb97-8954574a94e0/resourceGroups/rg1/providers/Microsoft.Sql/managedInstances/sqlmi02"
$managedInstanceList += $mi

# Select instances from the second subscription.
Select-AzSubscription 16d503da-a739-4ac9-9b3d-bfd82bec441f
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/16d503da-a739-4ac9-9b3d-bfd82bec441f/resourceGroups/rg2/providers/Microsoft.Sql/managedInstances/sqlmi10"
$managedInstanceList += $mi

# Create new trust group.
Select-AzSubscription 54afce91-1f05-4c0a-bb97-8954574a94e0
New-AzSqlServerTrustGroup -ResourceGroupName "rg1" -Location "West Europe" -Name "TrustGroup1" -GroupMember $managedInstanceList -TrustScope "GlobalTransactions, ServiceBroker" 

Or you can edit you existing Server trust group with Set-AzSqlServerTrustGroup, as shown in the example below.

$managedInstanceList = @()

# Select instances from the first subscription.
Select-AzSubscription 54afce91-1f05-4c0a-bb97-8954574a94e0
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/54afce91-1f05-4c0a-bb97-8954574a94e0/resourceGroups/rg1/providers/Microsoft.Sql/managedInstances/sqlmi01"
$managedInstanceList += $mi
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/54afce91-1f05-4c0a-bb97-8954574a94e0/resourceGroups/rg1/providers/Microsoft.Sql/managedInstances/sqlmi02"
$managedInstanceList += $mi
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/54afce91-1f05-4c0a-bb97-8954574a94e0/resourceGroups/rg1/providers/Microsoft.Sql/managedInstances/sqlmi03"
$managedInstanceList += $mi

# Select instances from the second subscription.
Select-AzSubscription 16d503da-a739-4ac9-9b3d-bfd82bec441f
$mi = Get-AzSqlInstance -ResourceId "/subscriptions/16d503da-a739-4ac9-9b3d-bfd82bec441f/resourceGroups/rg2/providers/Microsoft.Sql/managedInstances/sqlmi10"
$managedInstanceList += $mi

# Set the trust group.
Select-AzSubscription 54afce91-1f05-4c0a-bb97-8954574a94e0
Set-AzSqlServerTrustGroup -ResourceGroupName "rg1" -Location "West Europe" -Name "TrustGroup1" -GroupMember $managedInstanceList -TrustScope "GlobalTransactions, ServiceBroker"

If instances are in different VNETs then of course VNET peering needs to setup. On all VNETs, ports 5024, and 11000-12000 need to be allowed.

Usage examples for T-SQL and .NET

Once the preparations are over you can run distributed transactions from T-SQL, like in the example below. We are using linked servers to access all remote instances.

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
    INSERT INTO db01.dbo.t01 (tag, utc_time) VALUES (@app_tag, GETUTCDATE())
    INSERT INTO [sqlmi02].db01.dbo.t01 (tag, utc_time) VALUES (@app_tag, GETUTCDATE())
    INSERT INTO [sqlmi03].db01.dbo.t01 (tag, utc_time) VALUES (@app_tag, GETUTCDATE())
    INSERT INTO [sqlmi10].db01.dbo.t01 (tag, utc_time) VALUES (@app_tag, GETUTCDATE())
COMMIT

Also, you can run distributed transactions from .NET, like shown in the example below.

using (var scope = new TransactionScope())
{
    // Execute transaction on all 10 connections.
    //
    for (int i = 0; i < 10; i++)
    {
        using (var conn = new SqlConnection(sqlManagedInstanceConnStr[i]))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = string.Format("insert into T1 values(1)");
            cmd.ExecuteNonQuery();
        }
    }
    
    // Complete distributed transaction on all 10 Managed Instances.
    //
    scope.Complete();
}

Next steps

We hope that this new functionality will enable you to write new modern distributed applications or easily migrate your existing applications to Managed Instance.

If you have questions, feedback or additional feature requests related to distributed transactions, let me know in the comments! Happy distributed transactions everyone!