Can SQL Server log-based capture be used when source database is in the Simple recovery model?

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #8884

    Frank Knot
    Participant

    Can SQL Server log-based capture be used when source database is in the Simple recovery model?

    SHORT ANSWER:

    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.

    FULL ANSWER:

    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.

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

© 2019 HVR

Live Demo Contact Us