In a relation to MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group, which is a similar but different question.

We have an SQL Server 2016 for which we are considering creating an Always On availability group for purposes of disaster recovery only.
Currently the server is not a part of any cluster or availability group, and:

  • The server hosts multiple databases
  • Most of the operations are cross-database transactions
  • Many of the operations involve actions not supported in distributed transactions (e.g. save transaction)

From the fact that currently save transactions etc execute successfully, one can conclude that the server is not using distributed transactions.

Now, let us consider the following two quotes from Configure distributed transactions for an Always On availability group:

  • A transaction with two or more databases in a single instance of the database engine is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local transaction. SQL Server 2017 (14.x) promotes all cross-database transactions to DTC when databases are in an availability group configured with DTC_SUPPORT = PER_DB - even within a single instance of SQL Server.

    (That seemingly suggests that our cross-database transactions are technically distributed because they involve multiple databases. Which contradicts the observation that save transaction currently works, so let us call them "kind-of" distributed, not really "true" distributed.)

  • SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions.

    (This promises woes for distributed transactions executed when no DT protection is enabled in an AG. But does that include both the "kind-of" and the "true" distributed transactions, or only the "true" ones?)

Does the second quote address only "true" distributed transactions, or also the "kind of" distributed transactions that are "distributed" simply because they involve multiple databases, like the first quote explains?

That is, if the only "distributed" transactions that happen are the single-instance ones that just involve several databases from the same server instance, then is it or is it not the case that such "kind-of" distributed transactions are fully protected within an AG as if they were local transactions, even when the AG has DTC_SUPPORT = NONE?


Your Answer

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