There seems to be a new myth going around recently. I’ve had at least three people tell me, in the last month, that SQL’s transactional replication requires the database to be in full recovery.
This is complete fabrication. Replication (SQL native replication, that is) can work with the databases in any of the recovery models.
Transactional replication does involve the transaction log, as that’s where it picks up changes from. The log reader scans over the transaction log looking for log records marked for replication, copies those to the distribution database and then marks them as replicated. When the checkpoint (for simple recovery) or log backup (for full or bulk logged) occur, the log will only be truncated up to the oldest inactive, replicated transaction.
Because transactional replication has its own way of ensuring log records aren’t discarded before been picked up by the log reader, there’s no requirement for a specific recovery model.
Perhaps also worth mentioning:
SQL 2008’s Change Tracking also works with any recovery model. So DIY replication using change tracking and something like WCF is also independent of recovery models or when checkpoints occur.
Indeed. In fact, iirc, the only features that require a specific recovery models are Database Mirroring (full) and log shipping (full/bulk)
As I read your explanation, it makes more sense now. I’ll test this out. thanks!