A reader emailed me with a question: if you only have two drive arrays in a server (C and D, let’s say), and you also have an application on that server, how should you configure SQL Server? Best practices say to keep your data and log files on separate drive arrays, but is it OK to put them on the same drive and put the application files on the other drive?
The reason the data file (mdf) and log file (ldf) should normally be on separate drives is that when you’re doing inserts/updates/deletes, they both have to write at the exact same time. When the SQL Server engine starts to write data, it essentially:
- Writes to the log file that it’s going to change the data
- Writes to the data file
- Writes to the log file to mark that the transaction finished
That means putting those on the same set of drives is going to make writes much slower, because the writes will always be random: the disks will jump from the log file location to the data file location and back to the log file location. Random activity is slower than sequential activity, so the penalty is even worse.
So when is it appropriate to put the data and log files on the same drive, and use the leftover drive for something else?
- When the database is rarely updated. If it’s mostly read-only, you won’t incur the heavy penalty on writes.
- When one drive is dramatically faster than the other drive. Say you’ve got one RAID 10 array with 8 hard drives, and one RAID 5 array with 3 hard drives. It may make sense to have everything on the fast drive.
- When your application hammers TempDB more than anything else. I had a job interview once where the company said TempDB was more active than their data or log file drives by far. Ouch – in that case, it might make sense to put TempDB on its own array, and the user database data & log files on the other array.
- When the application does heavier disk activity than SQL Server. Notice that I said heavier disk activity: it’s not just enough to say that the application is used a lot, or does a lot of work – it has to be disk activity. Some applications just use the disk when they first start up, and in that case, they don’t merit a separate drive array.
Regardless of your decision, though, use Perfmon after the system goes live and track the drive activity. If one of the two arrays is being overwhelmed with load while the other one sits idle, then it’s time to rethink the decision. Moving data or log files is as easy as detaching the database, copying the physical file (don’t move it – something could go wrong) and reattaching the database. Granted, it involves downtime, but it’s better than being stuck with your decision for life.
Update on moving database files: JMKehayias, KBrianKelley and SQLDBA on Twitter all chimed in that users would be better off using ALTER DATABASE rather than a detach/reattach. At first I was suspicious, not seeing an advantage, but SQLDBA sold me when he said they’d moved a log shipped database that way: alter database to change the file paths, take it offline, copy the files to their new locations, and bring it back online. It kept log shipping intact. That’s a win.
More about SQL Server Data and Log Files
Should SQL Server use shared drives or dedicated drives on a SAN? I’ve talked about it in a few posts:
I would add that you need to consider recoverability. When the data and log files are on the same drive, you lose the possibility of being able to do a final backup of the tlog before a restore if the drives with the data files on it crash, because the log files are on the same crashed drives. So as long as a "point of failure" restore isn't required, then it could be acceptable for the data and log files to share the same drives.
Hi Brent,
Excellent article you have written. I’m wondering what implications are in store for those running in a virtual environment; little can be gained from separate drives when they are in fact just vmdk files. Have you any recommendations or experience with SAN or network shares?
Dallas
Hi, Dallas. Quite to the contrary – there are still a lot to be gained from separate drives as separate vmdk files. In a high performance environment, you may still want to put those vmdks on separate disks with separate performance characteristics. For example, you might place all of your SQL Servers’ data vmdk files on a separate type of array from their log vmdk files, because the data and log arrays have different performance characteristics. I’ve seen implementations where the data vmdks are placed on raid 5, and the log vmdks are placed on raid 10.
Regarding Tempdb,data, and log placement on an EVA. Since the Vraid is writing to all drives in the diskgroup anyway does it really matter if you establish another Vdisk for tempdb, vs. logs vs. data. I aan’t seem to justify creating lots of vdisks other than for specifically different Vraid types sicne they are all going to the same place anyway.
Hi Brent,
first, will there be notes from the SQL cruise on couching stuff suitable for Managers ( a post I did previously)…
second – if you have 2 drives C: & D; but these are VMDK files on the same huge SAN LUN because the VMDK files are on a ESX box, what then? Or do we try andmake sure the VMDK data disk file and VMDK log disk file are on different LUNS ( or use direct access ?)
Virtualisation seems to create a new layer of abstraction but I’m sure basic principals still apply of keeping heavily used drives on enough spindles whether this be via VMDK files or using direct access to LUN or otherwise……
is it worth covering SQL server in VMs on ESX as a separate topic? Its one that would attrract a lot of interest as I know a lot of people were intially scared off from putting SQL on VMs, but now with more grunty hardware and more efficint OSes, it seems people are now pushing that way. In fact, were looking at SQL 2008 R2 clusters across multiple ESX physical hosts….
Thoughts?
Zinto – thanks for your comments.
Unfortunately, the SQLCruise training is only available to people who attend the cruise. We have to charge money to cover our expenses, and if I gave that training away for free on the blog, then nobody would attend the cruise.
About the virtual data & log files, those are all good questions, and the answer is always, “It depends.” There’s a ton of variables – the type of SAN, direct attached storage, other servers sharing the same spindles, the HBA load balancing mechanism, and so forth. That’s beyond what I can cover quickly in the blog, unfortunately.