SQL Managed Instance DTC implementation – lessons learnt

Introduction

Azure SQL Managed instance (SQL MI) is now able to take part in distributed transactions using MS DTC, with the main documentation page at Distributed Transaction Coordinator (DTC) - Azure SQL Managed Instance | Microsoft Learn. While a common scenario has SQL Server initiating the transaction using BEGIN DISTRIBUTED TRANSACTION with linked servers providing the connections to the remote partners, we recently worked with a customer to move their workload to SQL MI, where the workload initiates a distributed transaction locally on the application VM, and then enlists its SQL queries into the existing transaction; here we share the lessons learnt during the implementation.

Networking

For networking, the prerequisite called out in the documentation is to have connectivity using the DTC ports (Port 135 and ports 1024-65535) in both directions between all participants in the distributed transactions – in this customers’ case, the SQL MI and the application VM.

For customers who don’t normally follow the deny-all pattern used in the default SQL MI network security group (NSG), this can be a surprise, so be aware that there is an explicit step required to add inbound and outbound allow rules for these ports to the NSG, with a priority that has these rules being used before the deny-all rules.

Other NSGs and/or firewalls in the path between the SQL MI and other participants will also need to be configured to allow these ports to communicate bi-directionally.

DNS suffixes and GPO

Another prerequisite is to add the SQL MI’s DNS suffix to each environment, using either the powershell cmdlet Set-DnsClientGlobalSetting or the DNS tab under the advanced TCP/IP settings for Windows machines, which is used to allow NETBIOS to resolve the shortname.

At many customers, this area of network configuration is controlled by Group Policy (GPO), meaning that the local windows adminstrators are unable to add these suffixes, and need to co-ordinate with the team managing group policies – so validate ahead of time on how this networking configuration is controlled to avoid delays.

SQL MI DNS settings

To allow the azure resources (including the SQL MI) to resolve private DNS names, the customer’s private DNS servers are added to the Vnet that the SQL MI is deployed in, either using the powershell cmdlet set-AzVirtualNetwork or the DNS Servers blade on the Vnet in the portal. However, changes to the Vnet’s DNS Servers aren’t automatically propagated to the SQL MI; you need to manually sync the SQL MI, either using Invoke-AzResourceAction -Action updateManagedInstanceDnsServers -Force (https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resolve-private-domain-names?view=azuresql&tabs=azure-powershell#update-sql-managed-instances) or the “Synchronize DNS server settings” button at the top of the Virtual cluster blade in the portal. Each SQL MI has a link to its virtual cluster on the overview blade for the SQL MI in the portal. We have seen that this is a necessary step otherwise DTC does not work, so it's worth doing this as part of any DTC implementation.

The list of DNS servers that the SQL MI will use can be checked by creating a SQL agent job with a CmdExec step to run IPCONFIG /ALL and view the results in the job history (pasting the output into your favorite text editor makes it easier to read).

MS DTC Authentication

For SQL MI, the only authentication option supported is “no authentication”, but for many customers their default VM build have the DTC authentication setting set to mutual authentication, so explicitly check on all participants that this is set to no authentication. For windows, this is in the component services app, under  My computer | distributed transaction coordinator | local DTC | properties | security (https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/distributed-transaction-coordinator-dtc?view=azuresql&tabs=azure-portal#limitations)

Testing

The tests called out in the documentation should be followed;

• Verifying the networking into the SQL MI using tnc DTC_FQDN -port 135 and tnc DTC_shortname -port 135
• Verifying the networking out of the SQL MI using a SQL agent job with CMDexec steps to run tnc AppVM_FQDN -port 135 and tnc AppVM_shortname -port 135

If the test to the FQDN fails, recheck the DNS server settings; replacing the FQDN with the IP address can help to see if the issue is with DNS, or with firewalls/NSGs blocking traffic. If the FQDN tests work, but the shortname tests fail, recheck the DNS suffix settings. Note that the PowerShell cmdlet Test-Dtc will fail in this context because it  tries to create a new CIM session on the remote target, which is not supported on SQL MI.