Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working

Question

I have a two-node SQL Server 2019 Availability Group with a database that has CDC enabled for several tables. The CDC is used to keep track of transactions that have not been captured for population into a data warehouse.

The Availability Group is configured with multi-subnet failover, with readable secondaries.

The application replicates transactions captured via CDC by using sp_repltrans to get a list of transactions, which it then reads via the undocumented function fn_dump_dblog. Once a transaction has been captured and integrated into the data warehouse, the app marks the transaction as replicated via sp_repldone. We have it configured to use ApplicationIntent=ReadOnly to connect automatically to the Availability Group's readable secondary node.

The capture/integrate process seems to be working well, and recovers nicely during an Availability Group failover, automatically switching to the other node as required to maintain a connection to a readable secondary. Transactions are being captured into the data warehouse.

Once transactions have been replicated into the data warehouse, the application executes sp_repldone on the secondary, which looks like it succeeds, but the transaction never gets marked as replicated on the primary, causing the transaction log to report REPLICATION in the log_reuse_wait_desc column of sys.databases.

The application calls sp_repldone like this:

EXEC sys.sp_repldone
        @xactid = NULL
      , @xact_seqno = NULL
      , @numtrans= 0
      , @time= 0
      , @reset= 1;

The above was captured via Extended Events sp_statement_completed. The Extended Events capture session is also configured to capture error events, and none are being reported.

If I manually run sp_repltrans to get a list of unreplicated transactions, then run sp_repldone against that list on the secondary, it appears to succeed without issue, however the primary still believes the transactions haven't been replicated. i.e. running sp_repltrans on the primary still shows transactions waiting replication.

I see nothing in the docs for sp_repldone indicating that it can or can't be ran from a readable secondary; indeed I would guess that it would return an error if it couldn't mark transactions as replicated, but perhaps there is a bug in the implementation. Admittedly, this configuration is probably not all that common. If we configure the capture/integrate process to attach to the primary node, by removing ApplicationIntent=ReadOnly, then executing sp_repldone against the primary does function correctly, and all transactions get marked as replicated allowing transaction log reuse.

Is there something that needs to be changed to make this work against the readable secondary?

---

I've reported the behavior via the Azure Feedback site here.

Answer 1

For background, I'm assuming this is a custom CDC solution, and the regular CDC jobs aren't running to populate the change tables and run sp_repldone, and so you must run sp_repldone to enable the log file space to reused after log backups. For most scenarios it would be simpler to use Transactional Replication to populate the target database, or use the built-in CDC solution and read the change data from the primary or a secondary database replica.

sp_repldone "Updates the record that identifies the last distributed transaction of the server.", which is stored in the database. So it definitely can't work on a read-only replica, and it's probably just undocumented what it does when you try.

And "A secondary replica cannot act as a replication publisher or republisher but replication must be configured so that the secondary can take over after a failover" Configure replication with Always On availability groups

So this is not a scenario that's supported with built-in transactional replication either. And the inability to succesfully mark transactions as replicated from the seconday may be the reason it's not supported.

It may work fine to read the transactions from the secondary, and connect to the primary to run sp_repldone, but you'd need to test.