Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
Thanks for getting back with more details.
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.
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
Thank you @SwathiPulagam for providing answers. It was very helpful.