Cross-Database Transactions Support For Database Mirroring or AlwaysOn Availability Groups (SQL Server)
Cross-database transactions and distributed transactions are not supported for database mirroring and for some configurations of AlwaysOn Availability Groups. There is no support for cross-database transactions for AlwaysOn Availability Groups on SQL Server 2014 or earlier. However, beginning for SQL Server 2016 Community Technology Preview 2 (CTP2), cross-database transactions are supported for AlwaysOn Availability Groups running on Windows Server 2016 Technical Preview 2.
There are several reasons why cross-database transactions are unsupported for database mirroring and some configurations of AlwaysOn Availability Groups. The main problem is that transaction atomicity/integrity cannot be guaranteed for the following reasons:
For cross-database transactions: Each database commits independently. Therefore, even for databases in a single availability group, a failover could occur after one database commits a transaction but before the other database does. For database mirroring this issue is compounded because after a failover, the mirrored database is typically on a different server instance from the other database, and even if both databases are mirrored between the same two partners, there is no guarantee that both databases will fail over at the same time.
For distributed transactions: After a failover, the new principal server/primary replica is unable to connect to the distributed transaction coordinator on the previous principal server/primary replica. Therefore, the new principal server/primary replica cannot obtain the transaction status.
The following database mirroring example illustrates how a logical inconsistency could occur. In this example, an application uses a cross-database transaction to insert two rows of data: one row is inserted into a table in a mirrored database, A, and the other row is inserted into a table in another database, B. Database A is being mirrored in high-safety mode with automatic failover. While the transaction is being committed, database A becomes unavailable, and the mirroring session automatically fails over to the mirror of database A.
After the failover, the cross-database transaction might be successfully committed on database B but not on the failed-over database. This would occur if the original principal server for database A had not sent the log for the cross-database transaction to the mirror server before the failure. After the failover, that transaction would not exist on the new principal server. Databases A and B would become inconsistent, because the data inserted in database B remains intact, but the data inserted in database A has been lost.
A similar scenario can occur while using a MS DTC transaction. For example, after failover, the new principal contacts MS DTC. But MS DTC has no knowledge of the new principal server, and it terminates any transactions that are "preparing to commit," which are considered committed in other databases.
Using Database Mirroring or Availability Groups together with DTC does not result in an unsupported SQL Server installation. If, however, a database is part of a Database Mirroring session or an Availability Group and DTC is also used in the database, support issues will be investigated by Microsoft only if unrelated to the combined use of Database Mirroring or Availability Groups with DTC. This does not apply for the supported scenario of AlwaysOn Availability Groups and DTC running on SQL Server 2016 Community Technology Preview 2 (CTP2) and Windows Server 2016 Technical Preview 2.
Cross-database transactions are supported with AlwaysOn Availability Groups beginning with SQL Server 2016 Community Technology Preview 2 (CTP2). The following requirements must be met:
Availability groups must be running on Windows Server 2016 Technical Preview 2.
Availability groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause. You cannot currently alter an existing availability group.