Full DTC Support for AGs Now in SQL Server 2017
SQL Server 2016 introduced support for the Microsoft Distributed Transaction Coordinator (DTC, or MS DTC) and Always On availability groups (click here for more info). However, what is in SQL Server 2016 is only half of the story. For a cross database transaction, it can happen either in the same instance of SQL Server (and therefore, as databases participating in a single AG), or to another database in another instance of SQL Server or some other data source like Oracle. The functionality in SQL Server 2016 covers the latter. That means that if you have two databases in an SQL Serer 2016 AG, you still have the old way – meaning, distributed transactions won’t work properly.
This has now been fixed as of SQL Server 2017 CTP 2.0. Somehow I missed this line in the release notes for CTP 2.0:
DTC support for Always On Availability Groups for all cross database transactions among databases that are part of the availability group, including for databases that are part of same instance.
As far as I know, this will not be backported to SQL Server 2016, but if you need full DTC support with AGs, it’s now there in SQL Server 2017 – a good reason to consider looking at it now. Want help with planning a SQL Server 2017 deployment on Windows Server or Linux? Contact us.