Cross-instance distributed transactions with Azure SQL Managed Instance

Published 04-29-2021 01:33 PM 1,347 Views
Microsoft

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.

2 Comments
Established Member

If it works like the "Cross-database for MI" good luck with that... Still in "preview" (which means buggy) since 2019.
Anyway, I will give it a try and I will comment here. 

Microsoft

Hi @fmantovani, I'm Sasa Popovic, program manager working on Managed Instance distributed transactions. Thank you for the feedback and interest in the feature! If you have more feedback please feel free to post in comments or to contact me via IM/email.
I'd like to address few points from your comment. Managed Instance distributed transactions are in public preview since October 2020. We consider public preview to be a public beta, meaning that anyone with Azure subscription can try the feature and Microsoft Customer Support Services will supply support services. Features in public preview are more likely to have limited functionality (e.g. you can put only two Managed Instances in Server trust group) than to be buggy, and I can tell that at this point we don't have any known outstanding bugs related to Managed Instance distributed transactions.

%3CLINGO-SUB%20id%3D%22lingo-sub-2297885%22%20slang%3D%22en-US%22%3ECross-instance%20distributed%20transactions%20with%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2297885%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20Azure%20SQL%20Managed%20Instance%2C%20you%20don't%20need%20MS%20DTC%20to%20run%20distributed%20transactions!%20You%20can%20now%20migrate%20to%20Azure%20or%20build%20distributed%20applications%20with%20Managed%20Instance%20which%20natively%20supports%20cross-instance%20T-SQL%20and%20.NET%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fazure-sql%2Fdatabase%2Felastic-transactions-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Edistributed%20transactions%3C%2FA%3E.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20recent%20modernization%2C%20distributed%20transactions%20are%20supported%20between%20Managed%20Instances%20as%20the%20only%20supported%20transaction%20participants.%20If%20your%20application%20runs%20.NET%20or%20T-SQL%20distributed%20transactions%20on%20SQL%20Server%20and%20of%20course%20requires%20MS%20DTC%20for%20that%2C%20you%20can%20now%20migrate%20to%20Azure%20SQL%20Managed%20Instance%20and%20distributed%20transactions%20will%20simply%20work.%20Also%2C%20if%20you%20need%20to%20build%20a%20multi-server%20environment%20in%20the%20cloud%20that%20needs%20to%20run%20queries%20in%20a%20transactionally%20consistent%20fashion%2C%20Managed%20Instance%20with%20native%20support%20for%20distributed%20transactions%20is%20a%20great%20place%20for%20your%20app.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDistributed%20transactions%20on%20Managed%20Instance%20can%20easily%20be%20setup%20with%20two%20simple%20steps%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20Server%20trust%20group.%3C%2FLI%3E%0A%3CLI%3EEnsure%20there%20is%20network%20connectivity%20between%20your%20instances.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EIn%20the%20rest%20of%20the%20blog%2C%20you%20will%20get%20more%20info%20on%20how%20to%20easily%20setup%20your%20Managed%20Instance%20to%20run%20distributed%20transactions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--471461949%22%20id%3D%22toc-hId--491805385%22%3ECreating%20a%20Server%20trust%20group%3C%2FH2%3E%0A%3CP%3EServer%20trust%20group%20(STG)%20is%20an%20entity%20that%20manages%20security%20requirements%20for%20running%20distributed%20transactions.%20You%20can%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fserver-trust-group-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecreate%20it%20in%20Azure%20Portal%3C%2FA%3E%2C%20or%20via%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fpowershell%2Fmodule%2Faz.sql%2Fnew-azsqlservertrustgroup%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20PowerShell%20%3C%2FA%3E%26nbsp%3Bor%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fcli%2Fazure%2Fsql%2Fstg%3Fview%3Dazure-cli-latest%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20CLI%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%20of%20Azure%20PowerShell%20script%20that%20creates%20STG%20for%20two%20Managed%20Instances.%3C%2FP%3E%0A%3CPRE%3ELogin-AzAccount%3CBR%20%2F%3ESelect-AzSubscription%20-SubscriptionId%20%22c829c89a-0931-4310-a49c-fa8f42a8cff2%22%3CBR%20%2F%3E%24managedInstanceList%20%3D%20%40()%3CBR%20%2F%3E%24managedInstanceList%20%2B%3D%20Get-AzSqlInstance%20-Name%20%22sqlmi1%22%20-ResourceGroupName%20%22rg1%22%3CBR%20%2F%3E%24managedInstanceList%20%2B%3D%20Get-AzSqlInstance%20-Name%20%22sqlmi2%22%20-ResourceGroupName%20%22rg1%22%3CBR%20%2F%3ENew-AzSqlServerTrustGroup%20-ResourceGroupName%20%22rg1%22%20-Location%20%22West%20Europe%22%20-Name%20%22stg1%22%20-GroupMember%20%24managedInstanceList%20-TrustScope%20%22GlobalTransactions%22%3C%2FPRE%3E%0A%3CH2%20id%3D%22toc-hId-2016050884%22%20id%3D%22toc-hId-1995707448%22%3ENetwork%20connectivity%3C%2FH2%3E%0A%3CP%3EFor%20distributed%20transactions%20to%20work%2C%20there%20must%20be%20a%20network%20connectivity%20between%20Managed%20Instances.%20If%20instances%20are%20on%20different%20virtual%20networks%2C%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DpSqDlQlcsLo%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EVNET%20peering%3C%2FA%3E%20needs%20to%20be%20setup.%26nbsp%3B%3CBR%20%2F%3EAdditionally%2C%20port%205024%20and%20port%20range%2011000-12000%20need%20to%20be%20allowed%20for%20outbound%20and%20inbound%20VNET%20traffic%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fvirtual-network%2Fnetwork-security-groups-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ENetwork%20Security%20Groups%3C%2FA%3E%26nbsp%3Bassigned%20to%20Subnets%20that%20host%20Managed%20Instances.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%3A%20when%20Server%20trust%20group%20is%26nbsp%3B%3CSTRONG%3Enot%20created%3C%2FSTRONG%3E%2C%20or%20there%20is%20no%20network%20connectivity%20between%20instances%2C%20distributed%20transaction%20will%20fail%20with%20following%20error%20message.%3C%2FP%3E%0A%3CPRE%3EMsg%208510%2C%20Level%2020%2C%20State%203%2C%20Line%2014%3CBR%20%2F%3EEnlist%20operation%20failed%3A%20Global%20Transaction%20failed.%20SQL%20Server%20could%20not%20register%20with%20Microsoft%20Distributed%20Transaction%20Coordinator%20(MS%20DTC)%20as%20a%20resource%20manager%20for%20this%20transaction.%20The%20transaction%20may%20have%20been%20stopped%20by%20the%20client%20or%20the%20resource%20manager.%3C%2FPRE%3E%0A%3CH2%20id%3D%22toc-hId-208596421%22%20id%3D%22toc-hId-188252985%22%3EDistributed%20Transaction%20examples%3C%2FH2%3E%0A%3CP%3EWith%20STG%20and%20network%20settings%20in%20place%2C%20you%20can%20run%20distributed%20transactions%20across%20Managed%20Instances%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Frelational-databases%2Flinked-servers%2Flinked-servers-database-engine%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Elinked%20servers%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EHere's%20an%20example%20with%20two%20SELECT%20queries%20against%20master%20database.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-indent-padding-left-30px%22%3ESET%20XACT_ABORT%20ON%0ABEGIN%20DISTRIBUTED%20TRANSACTION%0A%20--%20select%20from%20local%20server%0A%20SELECT%20*%20FROM%20master.sys.databases%0A%20--%20select%20from%20remote%2C%20linked%20server%0A%20SELECT%20*%20FROM%20%5Blinked_server_02%5D.master.sys.databases%0ACOMMIT%3C%2FPRE%3E%0A%3CP%3EHere%20is%20another%20example%20with%20INSERT%20queries%20against%20user%20databases.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-indent-padding-left-30px%22%3ESET%20XACT_ABORT%20ON%0ABEGIN%20DISTRIBUTED%20TRANSACTION%0A%20--%20insert%20into%20local%20server%0A%20INSERT%20INTO%20db01.dbo.t01%20(tag%2C%20utc_time)%0A%20%20VALUES%20('distributed_transaction_tag'%2C%20GETUTCDATE())%0A%20--%20insert%20into%20remote%2C%20linked%20server%0A%20INSERT%20INTO%20%5Blinked_server_02%5D.db01.dbo.t01%20(tag%2C%20utc_time)%0A%20%20VALUES%20('distributed_transaction_tag'%2C%20GETUTCDATE())%0ACOMMIT%3C%2FPRE%3E%0A%3CP%3EAdditional%20examples%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fazure-sql%2Fdatabase%2Felastic-transactions-overview%23combining-net-and-transact-sql-development-experience%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E.NET%20TransactionScope%3C%2FA%3E%20are%20available%20in%20the%20documentation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2297885%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20Azure%20SQL%20Managed%20Instance%20you%20don't%20need%20MS%20DTC%20to%20run%20distributed%20transactions!%3CBR%20%2F%3EYou%20can%20now%20migrate%20to%20Azure%20or%20build%20distributed%20applications%20with%20Managed%20Instance%20that%20natively%20supports%20cross-instance%20T-SQL%20and%20.NET%20distributed%20transactions.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2297885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Managed%20Instance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Apr 29 2021 01:54 PM
Updated by: