Not-Supported: AGs With DTC/Cross-Database Transactions

  • Article

When the topic of cross-database and/or DTC transaction support, under an Availability Group, comes up the quick response is NOT SUPPORTED!   This is a true statement and the conversation tends to then focus on – ‘but why'?’  In fact, some DBAs have tested various forms of these transaction types and not encountered errors.

The issue is the testing is not complete and the two-phase commit activity required can result in data loss or a database that does not recover as expected in certain configurations.  In fact, the SQL Server testers inject failures at strategic locations to create the scenarios that are difficult (but not impossible) to create on a production server.

DTC

DTC transactions rely on the DTC manager to help determine the outcome of the transaction.   When recovery occurs on a database the DTC manager may be contacted to determine if the transaction should be rolled forward or back.  

Today the problem with DTC transactions and Availability Groups (AGs) is one of resource management identification.    The SQL Server instance (IMPORTANT: which is different on the various cluster nodes) becomes a resource manager as part of the transaction.  When resolving a DTC transaction during recovery the SQL Server instance communicates with the DTC manager.  

For example:

  1. Primary Node 1 (ResMgrId = 1) enlists in a DTC transaction (ABC) but the transaction is not yet committed.
  2. Secondary Node 2 (ResMgrId = 2) receives log blocks for the transaction but redo can’t commit or abort the transaction because it has not been committed or aborted at the primary.
  3. Primary Now Fails (say a power supply goes bad on the primary) and the Secondary (Node 2) becomes the new primary.
  4. Recovery needs to bring the database on the new primary (node 2) into writeable state.   It knows about DTC transaction (ABC) but it is communicating with the DTC manager as ResMgrId = 2 and ResMgrId = 2 was not the SQL resource involved in the original transaction;  ResMgrId = 1 was the SQL Instance.
  5. Node 2 can’t determine the proper outcome of the pending DTC transaction.

Cross-Database

Cross database transactions use the same fundamental code as DTC transactions use.   The main difference is the lack of the DTC manager of coordination.

Instead SQL Server elects the lowest DBID in the transaction to be the transaction coordinator.  Shown here are the log records from a transaction that inserts a new row, in a table, in each database.

DBID = 1

Current LSN             Operation     
----------------------- ---------------
00000022:000000f7:0001  LOP_BEGIN_XACT
00000022:000000f7:0002  LOP_INSERT_ROWS
00000022:000000f7:0003  LOP_PREP_XACT        <--- (1) Written to controlling DB
00000022:000000f7:0004  LOP_COMMIT_XACT  <--- (3) Written to controlling DB 
00000022:000000f8:0001  LOP_FORGET_XACT   <--- (5) Written to controlling DB is not errors

DBID = 2

Current LSN             Operation     
----------------------- ---------------
00000022:0000012b:0001  LOP_BEGIN_XACT
00000022:0000012b:0002  LOP_INSERT_ROWS
00000022:0000012b:0003  LOP_PREP_XACT       <--- (2) Written to 2nd, … DBs
00000022:0000012c:0001  LOP_COMMIT_XACT  <--- (4) Written to 2nd, … DBs

Step 1: Write a prepare transaction to the controlling database.

Step 2: If controlling database LOP_PREP_XACT log record was written successfully write prepare in the 2nd, …databases.   Note:   The prepared log record may have a reliance on the controlling databases DBID (which could be different on a secondary replica instance.)

Step 3: If all prepared records are successfully flushed write a commit record.  This indicates that a COMMIT should not longer fail.  The space for the COMMIT was reserved during the BEGIN TRAN but a log file going offline or such action could occur resulting on offline database state(s).

Step 4: If COMMIT is flushed properly in the controlling database flush COMMIT records to 2nd, …. databases involved in the transaction.

Step 5: If there are no errors log that the transaction outcome can now be forgotten (LOP_FORGET_XACT).

Note:   If any of these steps encounter an error the database is taken offline and the controlling database may also be taken offline.

In the case of an Availability Group (AG) the log streams are separate entities.   They are shipped separately to secondary replicas.   As the steps indicate there is interaction between the controlling database and 2nd, …. database(s) that may be required to COMMIT or ROLLBACK a transaction.   This coordination is/may not present at the secondary replica location.   Even if the DBIDs are the same the log streams are separately synchronized and can lead to unexpected outcomes.

This means you could test a cross database transaction, force failover and everything work fine.   Test again it fails.   It could be as simple as the controlling DBID changes on the secondary and the outcome of the cross-database transaction can’t be properly determined.

For Availability Groups you should avoid the use of DTC and cross-database transactions.  Relying or using them could manifest as a problem with the database recovery activities and that is certainly not what you want in a HADR environment.

Bob Dorr - Principal SQL Server Escalation Engineer