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 limitations
Microsoft 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
- Cross-database transactions
After a failover, the mirrored database is on a
different server instance. Typically, the mirrored database is on a separate
server instance from the non-mirrored 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.
- Microsoft Distributed Transaction Coordinator (MS DTC)transactions
After a failover, the new principal server cannot connect to the MS DTC of the previous principal
server that uses the same resource ID. Therefore, the new principal server
cannot obtain the transaction status.
The 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:
- One row is inserted into a table in a mirrored database
- The other row is inserted into a table in another database
Database A is 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 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
Rule software||Rule title||Rule description||Product versions against which the rule is evaluated|
|System Center Advisor||SQL Server database mirroring is not supported with distributed and cross-database transactions||System Center Advisor checks if any database on the SQL Server instance is enabled for database mirroring. Advisor generates this alert based on this condition. Review your applications that connect to this database and make sure the SQL requests from this application does not participate in distributed transactions and cross-database transactions.||SQL Server 2008 |
SQL Server 2008 R2
SQL Server 2012
For more information about SQL Server 2005 database mirroring and cross-database transactions, visit the following Microsoft Developer Network (MSDN) Web site: