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
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.