Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Using database mirroring for cross-database transactions or distributed transactions is not supported in SQL Server
Article ID: 926150 - View products that this article applies to.
Database mirroring is not supported for either cross-database transactions or distributed transactions in Microsoft SQL Server 2005 Service Pack 1 (SP1)and later versions.
Database mirroring support limitationsMicrosoft SQL Server 2005 Service Pack 1 (SP1) introduced the database mirroring feature. However, database mirroring is not supported for either cross-database transactions or for distributed transactions. Transaction atomicity and transaction integrity cannot be guaranteed in the following scenarios:
Example scenarioThe following example scenario demonstrates how a logical inconsistency between databases might occur when you use database mirroring with cross-database transactions. In this example, an application uses a cross-database transaction to insert two rows of data. The following behavior occurs:
After the failover, the cross-database transaction might be successfully committed on database B but not on the failed-over database. This behavior can occur if the original principal server for database A does not send the transaction log for the cross-database transaction to the mirror server before the failure. After the failover, the transaction does not exist on the new principal server. Therefore, database A and database B are inconsistent. The data that is inserted into database B remains intact. The data that is inserted into database A is lost.
A similar scenario can occur when you use database mirroring with MS DTC transactions. For example, the new principal server contacts the MS DTC after a failover. However, the MS DTC has no knowledge of the new principal server. Therefore, the MS DTC stops any transactions that are in the "preparing to commit" phase, even though the transactions are considered committed in other databases.
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Collapse this tableExpand this table
For more information about SQL Server 2005 database mirroring and cross-database transactions, visit the following Microsoft Developer Network (MSDN) Web site:
Article ID: 926150 - Last Review: January 15, 2013 - Revision: 6.0