When configuring new SQL Servers on VMs, it is a good idea to have the Database, TempDB, logs, and backups on separate logical drives, even if the underlying storage is the same? I know that this is a good practice on physical servers; the separation actually helps us to reduce the pains caused by concurrent I/O.
I am trying to understand if having 3 drives instead of 5 would make sense. Can someone please provide their views/suggestions for this idea?
I like to separate them even if they are pointing to the same drives for disk space reasons. If I run out of space on my backup drive/mount point, it's not going to break the database (not right away at least).
This is how I usually separate things at a minimum:
Data files for user databases
Log files for user databases
Data file(s) for tempdb
Log file for tempdb
Data and log files for system databases, except tempdb
Backups (though I prefer to use a network share instead)
Each of the above would be a separate drive or separate mount point. Depending on the system, I might have separate devices for each database's data files.
But my systems have always used a SAN rather than local disks. If you had the choice between 3 drives or 5 drives, I would just ensure backups are separate and tempdb is separate. But again only for disk space reasons.
In addition to what Tara already said in her answer it's not only useful for disk space safety (you don't want a transaction log explosion causing your backups to fail or whatever).
It's also about being future proof. If everything is on it's own virtual disk, you could still decide if future requirements change to move one of the volumes to a new LUN, a new SAN, internal SSD's or whatever without too much of a hassle.
And as Aaron noted, separate logical drives won't help you on a physical box either if they are partitions on the same disk/RAID set. It can also help with SAN level backups or replications.
As long as all your volumes are on the same LUN on the same volume/SAN there won't be much performance difference by configuring everything on it's own virtual disk, but it gives you some resilience to disk space issues and it gives you some flexibility when you decide to move stuff around in the future.
