Setting Database Recover Model as SIMPLE - SQL Server Replication

[Anonymous]

While replicating data from SQL Server, DBA has concerns over making recovery model as Full Logging due to space issue.

Could you please assist me with the following questions?

1. Will there be an issue if the Recovery model is left as SIMPLE? If so, what exactly are they?

2. Are there any options we may pursue while keeping the Recovery Model SIMPLE?

3. How much space is typically filled by SIMPLE vs Full Logging vs Bulk Logged methods?

Thank you very much.

Qlik Replicate 

[PGN]

Simple recovery does not deliver data consistency.  Applying  change data capture relies on being able to read the source Tlog for changes.  Simple recovery truncates the Tlog once a transaction is committed.  Bulk logged uses less space in the Tlog, but the primary advantage is  for maintenance tasks (i.e., index rebuilds, etc.).  Not sure how much experience your DBA has, but worrying about tlog space is kind of ridiculous.  If the business requirements require accurate replication, then it's a non-starter.  Of course, all of this assumes transactional tasks, if all you're running is snapshot tasks, then it's fine.

[Anonymous]

Thanks for getting back with more details.

[SwathiPulagam · Accepted solution]

Hi @Jet,

Please find the answers below.

1. Will there be an issue if the Recovery model is left as SIMPLE? If so, what exactly are they?

A) Yes, there will be an issue if you use the SIMPLE recovery model and there is a high possibility that you will miss the data while replicating.

2. Are there any options we may pursue while keeping the Recovery Model SIMPLE?

A) You can try using MS-CDC but it is highly recommended to use a FULL or Bulk-logged recovery model.

https://help.qlik.com/en-US/replicate/May2021/Content/Replicate/Main/SQL%20Server/sqlserver_dbSettin...

3. How much space is typically filled by SIMPLE vs Full Logging vs Bulk Logged methods?

A) It depends on no. of transactions generated vs committed. You can opt for the Full recovery model and create frequent log backups so it will always maintain a small ldf file. In replicate read transactions from log backups.

Let me know if you have any questions.

Thanks,

Swathi

[Anonymous]

Thank you @SwathiPulagam  for providing answers.  It was very helpful.