Does it make sense to use full recovery if the data and log files are on the same HD?

Question

We have a database that was set up to use full recovery and my guess is that the reasoning was only to prevent data loss if a failure happened between full backups.

We make daily full backups of the database and we have no need to recover to any point in time previous to our last full backup.

Our data files and log files are in the same hard drive. From my experience (as programmer, I'm not a DBA), most database failures I've seen were related to disk failures and so I wonder if this setup makes any sense. I imagine that if the hard drive fails, we wouldn't be able to recover using the transaction logs.

So my question is twofold:

1. Is the most likely cause of a database failure, a hard drive failure, or are there other common reasons that would justify this setup ?
2. Would it make more sense to switch to the simple recovery model and convince the business that in the worst case scenario they would have to re-input data for the day ?

Answer 1

I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then that indicates the current approach is insufficient for their needs. If that requires hardware purchase to meet the RPO, then they will need to provide funding or accept their current max data loss. Finally, document the outcome in some public place and then test on your restores a recurring basis to ensure you are able to meet that RPO.

That said, there are plenty of other reasons to have data and log (and temp) on separate drives. Some of them documented on this question https://serverfault.com/questions/38511/ms-sql-layout-for-best-performance

Answer 2

First, to answer the questions directly:

1. In my personal experience, if I were keeping score, causes for database restores have been: accidental deletion of data = many, restore to dev for testing = many, IO system failure / corruption = none. (Knocks on wood)

2. You indicate you have "no need to recover to any point in time previous to our last backup". If that is in fact the case, then yes, switch into SIMPLE recovery.

Some comments, though:

Everyone's causes for failure will be different: developer talent may dictate frequency of accidental data deletion, storage architecture may dictate whether a failed drive is fatal or just a nuisance, etc.

As for SIMPLE vs FULL recovery model, that is a decision that should be made by the business folks. They need to weigh the damage to the business of downtime and / or lost data vs the cost of administration and space.

Regarding data and log files on the same drive, conventional wisdom had you separate the two because access to the data file is random while access to the log file is sequential. In my experience, this works if you're talking about a couple databases on drives that you can segregate, but on a SAN with 100 databases spread across 48 disks all data access becomes random anyway.

Answer 3

You definitely want to move the backups off of that disk for the reason you point out: disk failure happens. Having a local copy can be convenient, so that's not necessarily bad--for example, if an end user accidentally runs a delete query without a WHERE clause, having quick access to a backup can save your bacon. That said, I'd still put local backups on a separate drive; you don't want to run out of disk space on the drive which hosts your database files. In addition, there should absolutely be another copy off-site. Following the 3-2-1 rule helps keep you safe from resume-generating incidents.

Regarding the recovery model, if you do not need point-in-time recovery and take full backups often enough to satisfy your Recovery Point Objective, then Simple mode is fine. But make sure of that first--if it turns out your SLA guarantees 4-hour loss, then you'd probably want to keep the transaction log backups in there.

Answer 4

We make daily full backups of the database and we have no need to recover to any point in time previous to our last backup.

You answered the question right there. If you are certain you have no need to recover to any point previous to our last backup, which I took as 'we do not need point in time recovery, or the capability to recover in between our last full backup' then having the transaction log on the same disk won't matter in a recovery perspective. Set the DB to simple mode.

Transaction Logs are isolated to their own disks for 2 reasons:

• 1-Maximize write throughput. The transaction log must absolutely be written to (with the exception of a 60k buffer) before any data is committed. Having a fast write throughput is very important for your writes not to be slowed down.
• 2-Tail of the log backups. Assuming you take a transaction log backup every hour on top of the hour, and your drive array crashes 15 minutes before the next backup, you have just lost 45 minutes of work. However if you have your transaction logs on a different drive group, you can then do a final transaction log backup, restores your databases, and you're set to go! If the drive holding the log file also crashes then you only lost whatever was not committed to disk either by a lazywriter process or a checkpoint.