I’ve used replication and Availability Groups together before. Replication works great with an AG as both a publisher or a subscriber (though, not as a distributor–you can’t add the distribution database to an AG). I was surprised when I recently ran into a problem configuring replication on a database that was in an AG.
I was working on a SQL Server 2017 instance, which is still pretty new (it’s been out for 4 months), so it wouldn’t be unusual for me to run into a bug. It also wouldn’t be unheard of that I had screwed something up.
UPDATE: SQL Server 2016 SP2 CU5 and SQL Server 2017 CU14 include a fix to support savepoints for availability databases. I have tested & deployed this fix via SQL Server 2017 CU17. If you are experiencing this issue, install the latest Cumulative Update.
The error
I was attempting to enable a database to be a Publisher for transactional replication, when I got this error:
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
That’s weird. It worked in the QA environment, why won’t it work in Production. I had scripted & tested things in QA, so I couldn’t have screwed up too much. After some banging around, I realized that I got the same error on any database that is part of the Availability Group. If I ran the same command for a database that is not participating in the AG, it worked as expected. In the QA environment, I didn’t have an AG set up, so the AG seems to be the variable that is the consistent difference.
Read The F-ing Error Message
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
The first thing that stands out is mention of a distributed transaction. In SQL Server-land, a distributed transaction generally means coordinating cross-database or cross-server transactions. I’m not doing anything cross-server–I’m just running this system procedure to enable replication for the database. Except…it is. I’m running the procedure in master, and it’s updating my user database, and for all I know it’s doing something on the distributor. OK, so that’s a clue.
It also specifically mentions save points. Not sure what that has to do with anything, and it’s all coming from a system procedure anyway. I won’t be able to change the use of distributed transactions or save points within sp_replicationdboption, but these seem like valid points.
Let me back up a bit and add in some details I already knew. SQL Server 2016 introduced support for distributed transactions. The Distributed Transaction Coordinator(DTC) coordinates transactions that are distributed across multiple systems–just like the name says. Prior to 2016, distributed transactions generally worked, but weren’t officially supported. SQL Server 2016 included some changes (and 2017 some more) to allow for official support of distributed transactions, with some limitations. In particular, when you configure your AG, you need to specify `DTC_SUPPORT = PER_DB`.
I’ve got a new version, I’ve got an Availability Group, and I’ve got an error message mentioning distributed transactions/save points. Those three data points, plus my knowledge of recent changes helped me zero in on the problem.
1 + 1 + 1 = 3
I’ve already decided that replication isn’t happy about my AG. But why? My AG is properly configured to make use of the “Per database DTC support”, so that should be happy, right?
I knew there were some limitations to DTC support with Availability groups. Turns out one of those limitations is with save points. The Remarks section of the documentation for SAVE TRANSACTION has the following note:
SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.
Save points are not supported for distributed transactions. Oh. Now it’s all adding up.
- Replication system stored procedures use the DTC for distributed transactions.
- Replication system stored procedures use save points.
- Availability Groups should have per database DTC support configured to support distributed transactions.
- The DTC does not support save points in distributed transactions.
- Availability Groups, when configured with per database DTC support, do not support save points, which are used heavily by replication system stored procedures.
Pick two
If you’re using Availability Groups on SQL Server 2017 (and I assume 2016, though I haven’t tested it), you’ll need to pick between either per database DTC support or replication. My workaround was simply to disable per database DTC support so that I can get replication up and running. However, this behavior is not right.
I’ve logged a bug here. If you’re using replication & AGs, and want to have your distributed transactions to be properly handled, please up-vote the bug.
Thank you for the post. I have been having a lot of difficulty and this post helped me to resolve it. I had the same issue but with a twist. I had HA configured and was told to enable Change Data Capture for one database. It sees this as replication after a sort. It creates something like a log reader and monitoring job and I was getting the alert you mentioned above. I was finding nothing on the net to explain this so thank you so much for your assistance. It sucks, but I turned off the DTC for the AG and voila, it started working. Funny that Microsoft’s documentation doesn’t mention this issue. You are the man.
Thanks for your post Andy. Have you been able to try the fix (KB 4092554) included in SP2/CU1?
Sorry ! I have the same problem,My SQL Server is SQL2017. When I upgrade SQL2017 CU7，The problem still exist.
Thank you for this page, I have the same on my SQL2017. But when I upgrade SQL2017CU12(the last CU),The problem still exist. does KB4092554 not fix this problem?
For SQL 2016 SP2 CU5 should fix this problem