Why is SQL Server Simple Recovery Model Not Supported for HVR
Question
Why can't HVR support the Simple Recovery Model in SQL Server sources?
Environment
HVR 5
Answer
The Full Recovery Model is supported for HVR. For log based capture to work, we need to be able to read current and backup logs for the transactions HVR is working on. It is not recommended to use Simple Recovery Model because it does not allow for backup recovery. If anything interrupts HVR replication and HVR has to Rewind to search for the transaction it was working on prior to the interruption, it will not find the log or backup it expects from the Full Recovery Model. A full Reset and Refresh of the channel becomes necessary every time the transaction gets lost.
HVR manages the truncation point in the transaction log in such a way that the transaction log does not lose changes we have yet to capture. If there is native CDC replication occurring on the database at the same time, HVR will not be able to find the transaction if the truncation point has been moved by native CDC. There can be only one truncation point, and if HVR is running along with native CDC replication it will fail in Simple Recovery Model.
An example error message that will occur repetitively in this scenario is:
Capture will rewind back to 2023-03-23T13:13:21-04:00 and rescan 0s of changes.
F_JD0A42: Cannot find transaction log record for LSN [00038FEC:001B1C1E:0001]. The current TLOG is truncated already, and no matching transaction log backup is found.