Can SQL Server log-based capture be used when source database is in the Simple recovery model?
It should be possible when using the old transaction log access method (“SP”). By default, however, HVR uses a newer t-log access method (“FN”) which requires that the source database is in the Full recovery model.
Currently, HVR supports two method of reading data from the SQL Server transaction log. Internally we call the new one “FN”, while the old one is called “SP”. HVR uses FN method by default.
When using “FN” t-log access method, it is mandatory that source database is in the Full recovery model and a proper log archiving (log backup) procedure is setup. This is required to control transaction log retention- switching database to Full recovery model and making a full database backup ensures that transaction log will never be truncated off the data that has not been backed up (archived) yet. We then read the log backups if the data we need is no longer available in the current (active) transaction log file.
According to some online sources, SQL Server native transactional replication works with database that’s in either replication model (including the Simple model), so I guess our old t-log access method (“SP”) should work with database in Simple recovery model as well (though we have never tested this).
It may also be possible to implement an alternative log-retention (or, rather, log-release) logic for the “FN” by calling sp_repldone manually, rather then relying on the SQL Server Agent job. It may allow using “FN” method to capture changes from database in Simple recovery model. This approach has its own drawbacks, though.
Oops! Looks like you're using an outdated browser...
It appears you are still using Internet Explorer. Support for IE has been discontinued by Microsoft, and many features of our site may not display or work properly.
Please upgrade (it's free!) to Edge, Chrome, Safari, or any modern browser for the best user experience.