SQL Server MSDTC Support for Always On Availability Groups Cross DB

Since there is very little information available on SQL Server MSDTC Support for high availability scenarios, I thought I would write a quick post on the subject. You can check out this blog post from the SQL Server team at Microsoft, where they talk about availability group enhancements. If you are still looking for answers, then read along!

SQL Server MSDTC Support

Do understand there is (was) no support for DTC for Databases that are in an Availability Group, and where the databases are in the same instance. Here are some possible questions right now:

• Will this scenario be supported shortly?
• Do you have workarounds for this issue?  (combining failover clustering and AO on the same nodes)

Here is the official answer:

MSDTC between databases on same SQL Server instance is not supported with SQL Server AlwaysOn Availability Groups. This means that no two BizTalk databases in a distributed transaction can be hosted on the same SQL server instance. For transactional consistency, BizTalk databases participating in distributed transaction should be hosted on different SQL server instances. Note that it does not matter whether SQL instances are on the same computer, or different computers

Source

More information on this:

SQL Server 2016 and before: Support for cross-database transactions within the same SQL Server instance

In SQL Server 2016 and before, cross-database transactions within the same SQL Server instance are not supported for availability groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same availability group. +

Cross-database transactions are also not supported for database mirroring.

Here is the reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring

Conclusion

So yes, if you are planning to do distributed queries which are a part of an Availability Group, ensure that they are on different SQL servers.

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.