AlwaysOn AG and Cross-Database/Server Transactions We're about to move from SQL Server 2008 R2 Standard to SQL Server 2012 Enterprise and are reviewing or HA as part of process. Right now we use DPM and log shipping every 15 mins. Our MS partner is recommending AlwaysOn AG. I've heard of a lot of issues with AG, but the one that gives me the most concern is this one:

According to Microsoft, "Cross-database transactions and distributed transactions are not supported by AlwaysOn Availability Groups or by database mirroring". (http://msdn.microsoft.com/en-us/library/ms366279(v=sql.110).aspx)

We can't move to SQL Server 2014 (and it doesn't seem any better there anyway).

We have a 3rd party vendor app that uses 7 databases (bad design, but nothing we can do about it); many integrations that use multiple databases; and some that use multi-server transactions.

Is there a way to live with this limitation?

What do you guys do who use AG and have cross-database and cross-server transactions?

Regards

Mark
Is there a way to live with this limitation?


As of now, no not in a supported manner (I won't reference the link, as you have already done that in your original question).  There are potential inconsistencies that can arise from this configuration.  With that being said, there will be no hard enforcement on this, so you will not be prevented from the implementation.  But with the possible inconsistent pitfalls that may arise, I wouldn't and couldn't make the recommendation to go forward with AGs and distributed transactions.


  What do you guys do who use AG and have cross-database and cross-server transactions?


Currently, not implement AGs in this type of environment.