topic Re: Setting Database Recover Model as SIMPLE - SQL Server Replication in Qlik Replicate

Setting Database Recover Model as SIMPLE - SQL Server Replication

Anonymous — Wed, 05 Jan 2022 14:30:19 GMT

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.

Re: Setting Database Recover Model as SIMPLE - SQL Server Replication

PGN — Wed, 05 Jan 2022 14:52:39 GMT

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.

Re: Setting Database Recover Model as SIMPLE - SQL Server Replication

Anonymous — Wed, 05 Jan 2022 14:58:28 GMT

Thanks for getting back with more details.

Re: Setting Database Recover Model as SIMPLE - SQL Server Replication

SwathiPulagam — Wed, 05 Jan 2022 15:05:35 GMT

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_dbSettings.htm

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

Re: Setting Database Recover Model as SIMPLE - SQL Server Replication

Anonymous — Wed, 05 Jan 2022 15:08:12 GMT

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