SQL Server: Is Replication Recovery Model dependent ?

There is a myth that for replication to work properly the databases always have to be in Full recovery mode. Well that is not at all true.

First let me give a short overview on how replication works.

A snapshot agent creates a snapshot of the Publisher which is then taken up by the Distributor agent to apply any schema changes. Log reader agent then replicates transaction to the distributor after reading the log records which are marked for a replication and the distributor agent replicates them over to the Subscriber.

So now when a checkpoint occurs it will skip those records which are marked for replication. Once the distributor agent traverses the records to the Subscriber the transaction which were before marked as "Marked for replication" will be marked as "Replicated" by the log reader agent.

Now when the next checkpoint occurs these transactions will also be truncated. So it is not necessary that the recovery model always have to be in Full recovery model for all this to happen. As logging is done even in Simple recovery mode and maintained until the next checkpoint occurs.

But you have to be careful when you are performing the following actions if your database is in simple recovery model and is part of replication as output of these actions will not be replicated

• CREATE INDEX

• TRUNCATE TABLE

• BULK INSERT

• BCP

• SELECT. . .INTO

The reason ...Well the replication engine will not be able to pick up these changes as these changes will only log page allocations and de-allocations. You wont have to worry about the schema changes as these changes will be be replicated though they are in simple recovery model.

So the ideal strategy of Recovery model for setting up a replication environment from my point of view would be

1) Publisher database be in Simple recovery mode.

2) Subscriber be in a Full recovery mode.

Some people might argue that if Publisher is set to Simple then tlog backup wont be possible.

Well tlog backup on the Publisher wont be of any use anyways in Full recovery model because log backup's wont cover the records until they are marked as "Marked or replication" i.e records which still haven't been replicated to the Subscribers.

So it is better to have Subscriber in full recovery model and set up tlog backups over there which can save a lot of log space on the Publisher.

 

From:http://www.sqlservergeeks.com/blogs/Sachin.Nandanwar/sql-server-bi/135/sql-server-is-replication-recovery-model-dependent