Simple Recovery Is Simply Fine


Kalen Delaney states the following in her June 2003 Inside SQL Server column, "Inside Recovery Models" (InstantDoc ID 38510): "Simple Recovery is appropriate only for some of the system databases and for test or training databases that you never need to back up and can rebuild easily when needed." Many database professionals tend to knock the Simple Recovery model. Whenever someone in a newsgroup suggests using it, the first response is always along the lines of "you don't want to do that because you lose all recovery." That response is simply untrue on two points.

First, with the Simple Recovery model, you maintain the atomicity of transactions because no incomplete transactions are ever persisted; you never have physical database corruption as a result of failed transactions, system crashes, and so on. Second, with Simple Recovery, you have full media recovery to the last backup.

People have been happily using files and performing recoveries—meaning restoring the last backup—since the invention of the magnetic disk (or more precisely, the drum). The number of scenarios in which it is acceptable to restore to the last backup far exceeds the number of scenarios in which the customer has operational support for proper log management. So when should you use Simple Recovery? The answer is simple: If you require recovery to the last transaction, you need to use Full or Bulk-Logged Recovery; but if you require recovery only to the last backup, then Simple is simply fine.

Thanks for this important reminder! My column wasn't really about the pros and cons of choosing a recovery model but rather about how the different recovery models affect the transaction log. However, you make some valid points. Deciding which recovery model to choose isn't as simple as I made it out to be. For more comprehensive information about recovery models, you can read Michael D. Reilly's "Recovery Models" (September 2001, InstantDoc ID 21702) or my column "Database Recovery Models" (June 2000, InstantDoc ID 8551).

Compelling Performance


I enjoyed Russ Whitney's June Mastering Analysis column, "Data Discovery" (InstantDoc ID 38719), but he forgot to mention OLAP's performance advantages over relational reporting.

Years ago, I used Microsoft Access and Excel to provide key performance indicators (KPIs) to Intel engineers. Maintaining the multidimensional data in a relational database was incredibly complicated. My next job was supporting Hyperion Essbase at Nike, and I appreciated having an OLAP tool to do the job. We recently installed SQL Servers in our Nike stores and used Crystal Decisions' Crystal Reports to develop relational reports on the data. Now I'm one of two people trying to optimize the relational-reporting process. I attended Aspirity's Analysis Services Boot Camp more than a year ago and am hoping we implement SQL Server's free OLAP tools soon. The inefficiency of constantly rolling up transactional data for the reports is becoming more and more of a problem.

You're absolutely right: OLAP's performance is compelling. OLAP is designed for ad hoc analysis, and OLAP servers cache lots of information so that they can answer highly aggregated queries very quickly. Relational reporting products can also perform well, but their performance is focused on the repeated access of the same report. It's just a different design focus.