3

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?
5
  • Hi Josh, I blindly accepted the info provided to me, which is clearly incorrect. The information has been updated in the question. Thanks for the sharp eye. – Arthur Borsboom Mar 5 at 8:22
  • No worries, thanks for following up with the details! – Josh Darnell Mar 5 at 15:36
  • Impact: 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. – GSerg Mar 12 at 16:26
  • This apparently combines with the fact that in an AG, any cross-database operation, even within the same server, is promoted to a DTC operation. So there would be no failover guarantee for such operations. – GSerg Mar 12 at 16:28
  • This 'sounds' like the answer to the question: the impact is that the guarantee of a successful failover is lost. It might failover; it might not. – Arthur Borsboom Mar 13 at 21:18
0

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.

2
  • 1
    With regards to contacting the vendor, it is close to useless. The vendor has stated SQL clustering is not supported; they also have deprecated MS SQL in favor of their own database solution. Nevertheless, your answer has answered the question. I now understand the impact and I know where I stand. Thanks for your help. – Arthur Borsboom Mar 25 at 13:30
  • @ArthurBorsboom Yea sorry, I've worked with crappy vendors who didn't seem to know much about database development in my past too. Glad to be of help though. – J.D. Mar 25 at 13:52
-1

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

5
  • 1
    The specific functionality (create/update/delete users) does not work when DTC_SUPPORT = PER_DB, resulting in the error message. Ignoring the error message would mean I would leave this functionality broken. Changing the DTC_SUPPORT to NONE, makes it work. But at what cost? – Arthur Borsboom Mar 5 at 10:40
  • I guess that depends on the answer to - "Your first task is to work out which is the other party in your distributed transaction" – Stephen Morris - Mo64 Mar 5 at 10:43
  • What are the vendor like ? Will they give you a patch to remove the call to SAVE TRANSACTION ? Normally it is enough with BEGIN TRAN and COMMIT or ROLLBACK TRAN, creating a few users doesn't sound like it would need a savepoint but maybe there are 100 000s or a massive set of properties ? – Stephen Morris - Mo64 Mar 5 at 10:45
  • "Your first task is to work out which is the other party in your distributed transaction." I have no clue, since I have no insight in the application code. How can I determine the other party? The vendor states SQL Cluster is not supported. Getting support from the vendor is close to zero. This is unfortunate, since all seems to work when DTC_SUPPORT is set to NONE. I am searching for the options and their impact, including moving away from and staying with the cluster. For this last option this stackexchange question is meant. – Arthur Borsboom Mar 5 at 11:07
  • MSDTC is a component of the Windows OS - you can launch it as follows - In the Component Services, navigate to Computers-> My Computer -> Distributed Transaction Coordinator -> Local DTC. The you ought to be able to find out which is the other partner in MSDTC, I haven't worked with this for many years so don't know the details of the current version, I found an introduction for you here :- sqlshack.com/… – Stephen Morris - Mo64 Mar 5 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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