5

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over separate physical disks and RAID arrays is also an option. Simply put, more spindles is better.

When SQL server is running in a virtual environment, the concept of spindles applies differently. You often have little to no direct control over what physical disks your virtual disks are mapped to. Does this mean that it doesn't make sense to partition a database, or split the data and log files over separate virtual disks, the same way we do with physical ones?

I understand that there are other reasons to partition databases that still apply in virtual environments. For instance, large read-only tables can be in a separate partition that only needs to be backed up once, but this is not the subject of this post.

4
  • 2
    I didn't realize that a virtual environment no longer had separate disks? We have some new virtual environments being created where they will have one disk that uses SSDs and the others are not. To me that means you can have different physical disks for different drives even in a virtual environment. Oct 22, 2013 at 2:09
  • 1
    Virtual environment can have logical disks (as in different drive letters). However, these logical discs are created by an array of physical discs, and you really have no way of knowing how many physical discs are behind the logical disc. That being said, it is probably possible to have one logical disc based on mechanical array and another - on SSD array.
    – Joe Schmoe
    Oct 22, 2013 at 2:38
  • @Joe I'm not really sure what you're talking about here. You seem to be talking about data/log file organization and management, but the word you keep using is "partitioning." This wouldn't be such an issue, but you slip into a mild discussion on table partitioning in the last paragraph (and yes, I understand the notion of filegroups, etc).
    – swasheck
    Oct 22, 2013 at 14:47
  • He's really confused, we might want to close this as it's going to confuse others. He believes there are no physical disks in a virtualized environment and is asking how to store data across logical partitioned disks for performance.... Oct 24, 2013 at 19:27

4 Answers 4

3

This is an old question, but I came across this and was appalled at one particular comment made, suggesting the original asked was "confused". The question is perfectly clear, just not strictly about database administration. It falls into the fields of server virtualization and storage provisioning in virtual environments more than anything. It may well be that this question had been better suited for a different StackExchange site, but arrogantly dismissing the question because one does not understand the issue is unacceptable.

Let me attempt to explain the question better and give my best advice on the subject, in an attempt to leave something useful here for any others that may get dropped here by a search engine.

When deploying a database server, it has been considered best practice to put the OS, data files and log files on separate sets of disks. Just as a random example, let's say we have a server consisting of three RAID arrays:

2 HDDs in RAID 1 for the OS 4 HDDs in RAID 10 for the database data files 4 HDDs in RAID 10 for the database log files This setup would separate the IO and create separate points of failure for the data files and logs, as well as keep it separate from the OS. This was done for performance, resilience and maintenance reasons. Simple enough, right?

But what then, if we are deploying a database server in a virtual environment? There are no physical disks in a virtual environment. No there really aren't, unless you are mapping the virtual machine disk 1:1 to a physical disk, which is not what you'll see done in your typical virtual server farm. Even then, there would be a virtualization layer in between the physical disk and the virtual server, presenting the physical disk as attached to a virtual disk controller rather than the physical controller the disk is actually attached to.

So what is the problem? Let's take a small virtual server deployment as an example. A bunch of rack servers running in a virtualization cluster and one SAN with a bunch of disk groups in RAID 5 and RAID 6 with automatic storage tiering. All virtual disks are stored in the SAN in this example.

How do you go about assigning separate physical disk groups to your virtual database server now? Or maybe you don't? That was the question asked here. No useful answers were given, and understandably so, since it's a complex question where the answer depends a lot on your particular deployment. Some setups might give you very tight control over what kind of and what specific physical disks your virtual disks end up on, whereas others might be more of a black box that handles everything automagically.

In the simple "one-SAN" example I outlined above, I'd say that you don't really need to. But you might still want to. Even if you can't actually control what physical disks the IO ends up at, there are other benefits to splitting the data up. What if you migrate to a new virtual environment in the future, where you do get separate LUNs with different and known performance characteristics? If the data are already split over several virtual disks, moving them to new LUNs with the appropriate IO capability is much easier. In many hypervisors you can also give different IO priority to virtual disks. Again, this gives you some extra control. More accurate control over snapshot behaviour also becomes possible. Each database gets it's own separate underlying file system, that it doesn't need to share with other databases on the same server or even the OS itself.

Basically, this boils down to your performance requirements, size of the database, hypervisor and storage solutions at your disposal and many, many other factors. I hope I've pointed out a couple of useful tips. Plan ahead. While virtual environments with SAN/NAS solutions might seem like black boxes in many ways, many of them do have ways to achieve the same performance and redundancy goals as traditional direct storage.

1
  • 1
    Thank you for taking time to understand my question and give this elaborate response.
    – Joe Schmoe
    Jun 29, 2015 at 21:16
3

Partition-elimination is an awefully good reason to still partition even in a virtual environment, if your queries are such that they can use take advantage of it.

5
  • Hi Dave, I know the question was confusing but he was asking about separating physical disks, not partition elimination. I was confused too. Just a note, I didn't -1 your answer, someone else did. Oct 22, 2013 at 0:26
  • I was not asking about separating physical discs. There are no physical disks in virtual environment, hence the reason for my post.
    – Joe Schmoe
    Oct 22, 2013 at 2:01
  • 1
    Actually I would say this answer would apply to the Does it mean that for query performance reasons it doesn't make sense to partition database anymore? portion of the question as an additional performance reason in a virtual environment. Oct 22, 2013 at 2:07
  • Joe Schmoe there is ALWAYS a physical disk... ALWAYS. We don't store data in air yet. Where do you get the idea there is no physical disk? How will it even read the data if it is not in memory then? It seems like you have a lot of foundation work to cover. Oct 24, 2013 at 19:25
  • Of course there are physical disks but you are not accessing them directly. Behind your drive C: you can have pieces of multiple discs from some kinds of network connected storage. And placing you database on C: drive and log on D: in virtual environment doesn't mean they will actually end up on different spindles. Does this make it clearer?
    – Joe Schmoe
    Oct 25, 2013 at 20:31
2

I can attest that disks DO matter in virtual and non virtual environments, at the end of the day it's physics whether or not there is a Hypervisor layer added. Double true for writes, as they eventually have to persist to disk.

IF the data that is needed is not in memory within SQL Server (the base tables), and your query needs to read it, it will absolutely go to disk and read it.

For example, turn on your perfmon counters for disk sec/read & write as well as disk read bytes/sec & write bytes/sec. This will show you much how much data SQL Server is requesting. Thus, the matter of spindles always matters.

Separating transaction logs and data disks was important for 2 main reasons:

1-ACID compliance. If your data disks fail you can still take a 'tail of the log' backup, and restore the DB.

2-Transaction logs are heavy write and low read, data drives can be mixed or just heavy read. Separating them based on workload would be very helpful.

1

I think you might be a bit confused about virtualization. Which virtualization platform are you using? I haven't come across one yet that doesn't allow you to map the drives however you choose. There are default configs and settings that allow the hypervisor to decide how to best make use of the available host's resources, but I have never seen an offering that doesn't allow you to override and say "Map virtual drive C:/ to physical drive C:/, D:/ to D:/", etc.

To answer your question, no, if you have 2 virtual drives, C:/ and D:/, but they both map to physical drive C:/, you won't gain any advantage by splitting up your database files across the virtual drives. If you're also bringing in networked drives, unless you have an incredible network configuration, you're actually probably going to lose performance since writes to external drives are expensive and time consuming. If you need that performance gain, I would recommend creating a new virtual drive and mapping it to a different, non-networked drive on the physical hardware.

You will probably also want to make sure that you don't put another high-load DB on that virtual server with the same configuration, because if they're both trying to access the physical drive at the same time, you will lose performance waiting for read/write time on that drive while the other DB is busy using it.

Overall, if you are worried about performance to the point of adding additional physical drives and splitting your mdf and tlog files across 2 physical pieces of hardware (which indicates you're probably already having resource constraints), I would probably advise against virtualizing if at all possible. Highly transactional, resource hungry DBs are not the best candidates for virtualization, as you are inherently losing performance by splitting already scarce machine resources across multiple virtual clients and the hypervisor. The current setup I admin virtualizes everything except for the DBs for exactly this reason.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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