Microsoft KB Archive/926150

= Using database mirroring for cross-database transactions or distributed transactions is not supported in SQL Server 2005 =

Article ID: 926150

Article Last Modified on 11/10/2006

-

APPLIES TO

 Microsoft SQL Server 2005 Service Pack 1, when used with:  Microsoft SQL Server 2005 Standard Edition

 Microsoft SQL Server 2005 Enterprise Edition

 Microsoft SQL Server 2005 Workgroup Edition 

-

<div class="notice_section">

Bug #: 439321 (SQLBUDT)

<div class="summary_section">

INTRODUCTION
Database mirroring is not supported for either cross-database transactions or distributed transactions in Microsoft SQL Server 2005.

<div class="moreinformation_section">

MORE INFORMATION
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 distributed transactions. Transaction atomicity and transaction integrity cannot be guaranteed in the following scenarios:
 * 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.

Example scenario
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 (database A).
 * The other row is inserted into a table in another database (database B).

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 &quot;preparing to commit&quot; phase, even though the transactions are considered committed in other databases.

Keywords: kbhowto kbinfo kbexpertiseadvanced KB926150

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.