Database Mirroring and Cross-Database Transactions
New: 14 April 2006
Database mirroring is not supported with either cross-database transactions or distributed transactions. This is because transaction atomicity/integrity cannot be guaranteed for the following reasons:
- For cross-database transactions: After a failover, the mirrored database is on a different server instance, and typically it is on a separate database from the nonmirrored database. 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 is unable to connect to the distributed transaction coordinator of the previous principal server that uses the same resource ID. Therefore, the new principal server cannot obtain the transaction status.
The following 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.
See Also
Concepts
Distributed Transactions (Database Engine)
MS DTC Distributed Transactions
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|