Replication from simple mode database

[MartinCroft]

Hi

Anyone setup replication from a database in simple mode, I would assume it could have its issues but seems to work in a basic test and not found anything (yet) on the internet to say its a no one. theoritically if using transactional replication it should be replicating transaction in the log but with simple mode the log should be getting truncated on checkpoint anyone have any experince with this. I know one option is to change to full but this database is set to simple for a reason.

Cheers

 

[mrdenny]

Yes, transactional replication can be done with the publisher in SIMPLE recovery mode. I've done it, it works fine.

When the database is in simple recovery mode the log isn't actually truncated on check point. The log entry is marked for removal on checkpoint. When the database is being repliated if the transaction is for a table which is being replicated this flag is overwritten be a flag saying that the log data needs to be replicated. Once the log reader agent reads the transaction that bit is flipped. Once the bit that says the transaction has been checkpointed the log entry can be removed by SQL when it does it's log cleanup.

(This is all theory based on what I've seen SQL Server Replication do. Since I don't work for Microsoft I don't have the actual lowdown of the transaction log.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com