I am currently using Sql Server 2017 (14.0.3294.2) running on Windows Server 2016 as an HA cluster, one primary with two secondaries.
A few days ago, the primary was rebooted and I think the person that did it executed some operations that made the primary server primary again. Since then, CDC stopped working. Querying the cdc tables doesn’t offer a clue about the status. The CDC job is enabled.
I found this post: https://stackoverflow.com/questions/23744083/cdc-is-enabled-but-cdc-dbotable-name-ct-table-is-not-being-populated, however, I am very reluctant to use the solution in this context.
The last error in the CDC job log is (after the primary was restarted):
Executed as user: UserName. Failed to update database "DatabaseName" because the database is read-only. [SQLSTATE 25000] (Error 3906). NOTE: The step was retried the requested number of times (10) without succeeding. The step failed.
Any suggestions on how to make CDC work again?