A new SQL myth
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.
Subscribe in a reader
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.
William Brander said this on December 9th, 2008 at 09:26
Indeed. In fact, iirc, the only features that require a specific recovery models are Database Mirroring (full) and log shipping (full/bulk)
Gail said this on December 12th, 2008 at 13:26
As I read your explanation, it makes more sense now. I’ll test this out. thanks!
tnk7200 said this on July 9th, 2011 at 23:50