With 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.
Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server Installation – standalone SQL installs with Storage Replica,floating IP and manual failoverPosted: January 12, 2016
Following on from the previious attempt at Windows Server 2016 Storage Replica with Cluster Shared Volumes which did not work completely I tried a manual approach.
I did not give up, what if we do not use Windows Clustering but do all the steps manually?
This is 2 standalone SQL Servers both installed with data on the F: drive and then Storage Replica used to keep them in sync.
– add a DNS entry and a floating IP address
– manually setup Storage Replica
– manually handle stopping/starting services, failing over/back the Storage Replica and moving the floating IP
does it work?
The answer is yes-ish if you are careful and ignore the warning about data loss!
There does not seem to be an option to failover (Set-SRPartnership) synchronously to guarantee no data loss!
Also we could do with an option to validate everything is in sync or resync even if we would have to suspend writes to allow this.
Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server Installation – standalone SQL installs with Storage Replica,floating IP and manual failover