A few weeks ago one of my blog readers asked in a comment if it is a good idea to set the Recovery Model of the model database to SIMPLE, and if there are any downsides of that approach.
I can’t emphasise it enough, but using the Recovery Model SIMPLE for a OLTP workload (which is typically read/write) is a really bad idea. And even setting the Recovery Model of the model database to SIMPLE is a really …… idea! (fill in the missing word). But I don’t want to cry in this blog posting about that fact, I just want to show you what the side-effects are when you run your OLTP databases in the SIMPLE Recovery Model.
Your Backup/Restore Strategy – it’s simple!
When I perform my SQL Server Health Checks, or when I’m doing on-site customer consulting engagements, I sometimes see production OLTP databases running in Recovery Model SIMPLE. It just freaks me out! You are playing with your life and your job!
When I then discuss that problem with the DBAs, they mostly say that they have set the database to SIMPLE because they had a problem with an ever increasing Transaction Log. A lot of people out there don’t know that you also have to perform regular Transaction Log Backups to keep the size of the Transaction Log manageable. That’s just one very important prerequisite of the default Recovery Model FULL: regular Transaction Log Backups!
In the Recovery Model SIMPLE you don’t need any Transaction Log Backups (they are also not possible), because SQL Server itself manages the Transaction Log for you, and just overrides it regularly. As a side-effect you don’t need Transaction Log Backups and your Transaction Log doesn’t grow.
But what’s the downside? You can’t perform Transaction Log Backups! Regular Transaction Log Backups are the most crucial part of any OLTP database – regardless of which RDBMS system you are using! When you are running your database in the Recovery Model SIMPLE you can only perform Full and Differential Database backups. That’s it!
But imagine now that your last Full or Differential Database Backup is already a few days (or even weeks!) old, and because of some problems (user errors, corrupted database, etc.) you have to restore your database. Wow! Your data loss will be huge, because everything since the last backup will simply be lost!
But when you perform regular Transaction Log Backups (when you use the Recovery Model FULL), the situation is quite different when you have to restore your database. Because after you have restored the last Full and Differential Backup you can also restore all the various Transaction Log Backups that have happened afterwards. This means that your possible data loss is probably quite small as long as you have performed regular and frequent Transaction Log Backups.
How frequent? It depends on how much data you are prepared to lose. That’s your so-called Recovery Point Objective (RPO). It’s mainly a requirement dictated by your business. I have worked with customers who have done Transaction Log Backups every hour, every 15 minutes, or every minute. And there are even SQL Server databases out there where Transaction Log Backups are just running continously: one backup after the next one. It really depends on your RPO and how much money you want to invest. A „no data-loss“ (which every business always wants initially…) is almost impossible and would be very expensive…
Your HA options – there are no options!
When you run your database in the Recovery Model SIMPLE your HA (High Availability) options are also very limited. Almost every modern HA technology in SQL Server (AlwaysOn Availability Groups, Database Mirroring, Transaction Log Shipping) depend on the Transaction Log and on regular Transaction Log Backups. If you want to use Availability Groups or Database Mirroring you have to run your database with the Recovery Model FULL. No other Recovery Model is supported and would also not be technically possible.
If you want to use the good old Transaction Log Shipping (which is still a great technology!) you have to run your database at least in the Recovery Model Bulk Logged. If you are using SIMPLE you just can’t use Transaction Log Shipping. Just think about these side-effects the next time that you want to change the Recovery Model of your database.
To be honest: if you have production OLTP database and you care about your data, you HAVE to use the Recovery Model FULL. There are no „it depends“ recommendations. And even setting the Recovery Model of the model database to SIMPLE is not really a good idea. So please think about all these very important things the next time you are thinking of setting the Recovery Model to SIMPLE…
Thanks for your time,