6

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?

  • 1
    What virtualization system is this? What kind of storage system are the VM drives stored on? – Max Vernon Jan 25 '17 at 19:53
  • Just FYI, I don't know that separate drive letters can help performance or concurrency in all scenarios on physical systems, either. If the underlying I/O is the bottleneck, separate drive letters won't change that. – Aaron Bertrand Jan 25 '17 at 20:02
  • 1
    To add to this discussion, can mount points be used as a replacement to separate volumes? – SQL_Underworld Jan 26 '17 at 5:28
5

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.