3

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

Improve this question
2
  • Just a thought, you can if possible use SQL Server Many-to-One replication to get rid of all the cross database & server queries and then make that database part of AG.
    – Kin Shah
    Commented May 21, 2014 at 13:38
  • @Kin Thanks for reply. Unfortunately, many of the cross database transactions are in a 3rd party vendor app, so we're hosed on those. Many of the cross server ones are for systems integration: for example when App1 on server 1 creates a pallet, we need to create it in the Logistics App's database on server 2 (different sites) and mark as transferred (so insert and update). Just one example
    – mark1234
    Commented May 21, 2014 at 17:15

1 Answer 1

Reset to default
2

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.