System.Transactions (and AlwaysOn Availability Groups)
Posted: April 24, 2016 Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized, Windows Server 2016 | Tags: Microsoft SQL Server, Microsoft SQL Server 2016, Windows Server 2016 Leave a commentWith SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) supports MSDTC which can be used for transactions which access more than 1 database.
However there are Transactions which do not use MSDTC.
Within the .NET Framework there is System.Transactions namespace https://msdn.microsoft.com/en-us/library/system.transactions%28v=vs.110%29.aspx
As per https://msdn.microsoft.com/en-us/library/ms229978%28v=vs.110%29.aspx these implement a Transaction Manager which can handle transactions within SQL Server.
System.Transactions only SOMETIMES escalates to MSDTC!
“As long as the System.Transactions infrastructure handles..at most one durable resource that supports single-phase notifications, the transaction remains in the ownership of the System.Transactions infrastructure”
The question then is with SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) and a transaction which uses more than 1 database
a) Does SQL Server 2016 AlwaysOn Availability Groups support single-phase notifications?
b) With SQL Server 2016 AlwaysOn Availability Groups does moreĀ than 1 single database count as 1 durable resource or more than 1 durable resource?
As per http://msdn.microsoft.com/en-us/library/ms229979.aspx tracing can be used to determine who promotes a transaction and why.