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