For those of you who have had me in class before, you have heard me talk about the benefits of applying the SIMPLE recovery model. And you have heard me urge its use if you can get away with it. You have also heard Microsoft write about the benefits of complex multi steps DR plans for huge enterprises. Recently I ran into a situation where an approach right in the middle was called for, and I’d like to share that with you.
Back up and restore scenarios are all about 2 things. “Mean time to recovery” and “Acceptable data loss”. Weaved into the art of leveraging these components are idiosyncrasies to your own environment. Recently I ran into this environment...
ENVIRONMENT:
12 gig Mission critical database
Restore operations MUST be quick and simple
24 hour data loss is acceptable
Disk space for retention of backups is limited (about 60GB available)
Daily transactions touch about 2-10% of the total size of the database (through inserts or updates, deletes almost never occur)
A traditional backup up schedule of a complete every night will not be sufficient, it will provide simple restores and handle the 24 hour data loss requirement, we simply will not have enough space for sufficient retention of backups.
On the other side of the spectrum , we could change to FULL recovery model and run a complete weekly, run transaction logs backups every 15 minutes, then run differentials every night. This option is unacceptable because the restore process is not simple enough, yet it does handle the disk space issue.
Consider this setup. Stay with the SIMPLE recovery model. Set up weekly complete backups and set up daily differential backups. This keeps restores relatively easy, it will always be a two step process – restore the complete then restore the most recent differential (no transaction logs). It also conserves disk space as we only have one 12 GB backup (the complete) per week, and the nightly backup is a differential of no more than several hundred MB (though this will grow throughout the week).
If you have a relatively large database that is mission critical but has few inserts into it daily, and quick/easy restorations is of utmost importance, consider the approach I just went over.
I'm curious what is the scenario where 24 hour data loss is acceptable? Those Insert and Update transactions are probably pretty important especially in a small business scenario. Losing orders, or changes in customer information in the last 24 hours is probably not an acceptable loss, depending on what the issue problem is. There are several tools that make it super simple to restore from multiple back up files, I'm not sure that the number of files makes for more difficult to restore scenario. Also if a business cares about disaster recovery though should be willing to invest in storage space for backups. Storage is cheap. Although I like the idea of simple recovery I don't know of many practical applications.
ReplyDeleteAwright18, thanks for your viewpoint. There are many scenarios where 24 hour data loss is acceptable, particularly within the SMB market. Consider a small doctor’s office, where an application supports new patient registrations. As the business is neither consistent nor able to be trended, there might be times where 24 hours worth of data loss translates to one new patient needing to be re-entered. You also bring up a common point, when developing a DR plan - the question, “How much data loss is acceptable?” must be asked. Naturally every stakeholder will always answer the same way, which is just as you have, “No data loss is acceptable.” Consider the difference between the following statements; “No data loss is acceptable”, “No data loss is ideal”. If, truly, no data loss were acceptable, then DR would be every organizations number one goal, even more important than growing the business, servicing accounts or payroll obligations. This plainly is not the case. Therefore data loss is something to be managed not mitigated. Taking the time to determine how much data loss is acceptable, is a mature DR step that will allow SLAs to be met and in some cases exceeded.
ReplyDeleteA data recovery plan, and the step taken at the time of data recovery, are often a mixture of sound practices, combined with a specific contextual environment – as such you may have to look at several permutations of a DR plan, to achieve a real measurable business benefit.
-Ed Dressler edressler@centriq.com
Very good article. You explained 3 database restore plan and benefits in effective way.I am so relaxed to see your article otherwise mostly I got only little bit information which is sometimes create the confusion.Thanks.
ReplyDeleteelectronic signature software