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!
Hi Gail. I hope this note finds you and yours in good shape.
Old thread, I know, but I’ve not ever had to setup any form of replication but that may be coming up soon. With that, I have ask, will “Minimally Logged” events, such as Minimally Logged Inserts and Minimally Logged Index Rebuilds in the BULK_LOGGED Recovery Model, replicate successfully? This ol’ brain o’ mine says “no” but I have to ask because I’ve never had to work with replication before.
Thanks for your help here.