MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group

Question

A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit, 14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary.

A closed source application used by this company seems to work fine with the exception of one piece of functionality. When this functionality is used, the following error can be found in its log file:

Cannot use SAVE TRANSACTION within a distributed transaction

A possible workaround, according to an unrelated website, is to disable distributed transactions by:

ALTER AVAILABILITY GROUP MyaAG
   SET ( DTC_SUPPORT = NONE );

After testing, this seems to solve the issue. However, I am uncertain about the impact of this change.

• What impact does this change have?
• Is the cluster still active?
• Is the data still replicated to the secondary server?
• Does it have a positive or negative impact on the performance?
• Has a safety feature just been disabled?

Answer 1

So unfortunate news for you is it sounds like this closed source application uses a potentially deprecated feature of SQL Server if it's using SAVE TRANSACTION, and was not designed to run for a database that's part of an Availability Group (or any kind of clustering for that matter). Even more unfortunate is your only three options are:

1. Thoroughly verify it isn't breaking anything when you use that functionality of the application. That should involve testing that functionality and measuring the results on your primary, and then failing over to your secondary and verifying the same results did in fact commit to that node as well, and the application is still working as expected on the secondary. If all's well, then just live with the error.

2. Reach out to the vendor and communicate to them that you're trying to run their application on a clustered server with an Availability Group and are receiving an error for a specific function of their application that appears to use a deprecated feature of SQL Server, and that you need their confirmation if this is ok, and/or does their application support Availability Groups. You'll probably want do this anyway, if you pick option 1.

3. Disable distributed transactions with SET ( DTC_SUPPORT = NONE ); as you've seen this removes the error, but it does make your Availability Group less reliable which I'll answer directly to your questions below.

Out of these options, your best bet is #2, so that you can work with the vendor and let them know the full situation in which you're trying to use their application. It sounds like Availability Groups aren't directly supported by it and they'd be able to confirm that. Unfortunately for you, that's just a constraint from the vendor, and there's not much else you can do other than the options I provided above, and/or don't use Availability Groups / clustering, rather have an alternative high availability plan in place such as maintaining a completely decoupled, second server, which you regularly restore backups to. (Obviously this is less than ideal.)

To directly answer your questions on disabling distributed transactions:

What impact does this change have?

There is a risk of data loss from unhardened (uncommitted) transactions when a failover event occurs from the primary to the secondary. While this risk is potentially small, and I believe only affects your distributed transactions themselves (such as this application functionality with the SAVE TRANSACTION code) it's still a risk. This is discussed in the Microsoft Books Online for Configure distributed transactions for an Always On availability group, specifically when they say:

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.

Is the cluster still active?

Yes the cluster is still active and you should be able to easily test that by making a minor data change on the primary and watching it update on the secondary.

Is the data still replicated to the secondary server?

Yes, same answer as above.

Does it have a positive or negative impact on the performance?

Not relevant to the change, and probably measurably no difference.

Has a safety feature just been disabled?

Yes, your distributed transactions are at risk for data loss now if a failover were to occur when they were still active transactions (i.e. the secondary now becoming the primary wouldn't know if it's correct to rollback those transactions, rollforward or commit them), as discussed in my first answer to your questions. How much risk this is to you will depend on how important that data is for the feature that uses the SAVE TRANSACTION code, and anywhere else you may be explicitly using distributed transactions.

Answer 2

I think you can ignore the error, Save Transaction is used to create a savepoint in a transaction (but this is not allowed for distributed transactions), you lose the ability to do a rollback to the savepoint. The only one who will really know is your vendor. But I would not disable distributed transactions - If you disable the distributed transaction then you won't enlist the 2 parties into a transaction at all. Your first task is to work out which is the other party in your distributed transaction - it could be another SQL Server, Oracle, MSMQ (unlikely but that's the last time I worked with MSDTC in earnest). Then you can assess if the distributed transaction is really required.

When you get this error what else happens ?

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver15