Many applications use different databases that are connected, sometimes using code but also using database connections. In time a lot of cross-database queries or stored procedures that reference other databases may have been created and used. Maybe distributed transactions have come in use. These structures work very well in a scenario with only a single database-instance or a clustered installation of SQL Server (FCI). As soon as high availability is considered, Availability Groups are the preferred option because of the many advantages this solution offers. Not everyone realizes though that although this technique is a good fit for a lot of databases, it’s not always the best choice.
Installation and configuration of Availability groups seems simple and fast. It has many advantages but it can also lead to unexpected issues that may be discovered when it’s already too late. Many companies discovered this the hard way.
AlwaysOn Availability Groups in SQL Server do not support cross-database transactions (yet), DTC-transactions support is limited. The exact limitations depend on the SQL Server version:
SQL Server 2014 and 2012
No support for cross-database transactions or DTC transactions. When using these in an availabilitygroup there is a risk of dataloss and corruption. Even more important: there is no way to discover it, the problem is caused by the way availability groups work; it only gets visible when you try to read data that’s not there or that is not consistent. At that time no repair will be possible anymore.
SQL Server 2016
Support for cross-database transactions and DTC-transactions is very limited and only supported in specific situations. See the following article to prevent unexpected surprises: https://msdn.microsoft.com/en-us/library/ms366279.aspx
SQL Server 2017
MSDTC-transactions are nearly fully supported. Cross-database transactions are supported too, but both have apparent limits when using database-mirroring and (therefore also) availabilitygroups. More information: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring