Monday, February 08, 2010

Can we have database on SIMPLE Recovery model for Transactional Replication?

Yes. We can have Publisher database on SIMPLE recovery model.

The reason is that;

  • When executing, the Log Reader Agent first reads the publication transaction log and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data transactions that have been marked for replication. Next, the agent batch copies those transactions to the distribution database at the Distributor.
  • The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
  • Only committed transactions are sent to the distribution database by LogReader agent.
  • After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed.
  • Finally, the agent marks the rows in the transaction log that are ready to be truncated. Rows still waiting to be replicated are not truncated
  • The transaction log on the Publisher can be dumped without interfering with replication, because only transactions not marked for replication are purged.