Sql-server – Cross Database transactions with Availability Groups

availability-groupsmsdtcsql-server-2012

Recently we are working on a POC to get Always on work and happened to see this article in BOL

http://technet.microsoft.com/en-us/library/ms366279.aspx

This article suggests that there would be logical inconsistency when we are dealing with Synchronous mode too, but will this actually be the case?

Consider for example databases A and B on which the transaction is running and A is in High-safety mode and B is not mirrored. The log of A has to go to Mirrored database then the Primary database commits eventually two phase commit(transaction on B) succeeds but article suggests that log will not be transferred in the first place and results in commit on B which is contradictory. Please help me in understanding Whether the statement suggested in above article is true. If yes how can it be :).

PS :Please let me know if I need to provide more information around this.

Best Answer

The article isn't well worded but here's what I think it's saying

Beginning scenario - DatabaseA is in an AG and is currently primary on Server1 and can run on Server2. DatabaseB can only run on Server1

T1 - Transaction begins T2 - Row inserted into DatabaseA (log not yet sent to Server2 though) T3 - Row inserted into DatabaseB T4 - Transaction commit called T5 - DatabaseB commits transaction (since the two DBs commit individually) T6 - DatabaseA fails over before log is sent

Since the log was never sent DatabaseA wouldn't have the row but DatabaseB would. If synchronous mode is being used that can't happen because the commit can't be called until the log from the row is sent. However, it may be possible for the commit on DatabaseB to happen and DatabaseA to fail over before the commit happens there. This may lead to a rollback in DatabaseA instead of a commit. I can't say with certainly that you can get into the same situation with synchronous mode but if you can that's how it would likely happen.