SQL drive assignations
-
I know that common knowledge is to assign your SQL MDF and LDF files to a different drive than the OS, but is that thought process valid in a VM-based world? When it was established as a best practice to do this, we were using physical machines and the reasoning, as I recall, was to separate the IO for SQL so that there was no contention with the OS. Now, it's all going to the same bucket (SAN or DAS, but all the same VM datastore, unless you have different datastores dedicated to MDF/LDFs). As such, is the thought behind using different drives still valid?
-
@Grey said in SQL drive assignations:
I know that common knowledge is to assign your SQL MDF and LDF files to a different drive than the OS...
It's common knowledge from the RAID 5 spinning disk era of the 1990s. And even then it was only on NT4 when NTFS was overly fragile.
-
@Grey said in SQL drive assignations:
... but is that thought process valid in a VM-based world?
Nothing to do with VMs. It's all about storage. And since Windows 2003, NTFS has been ridiculously stable so the filesystem reason to split is long since gone. And the array problem went away once we moved to RAID 10 and/or added cache RAM onto RAID cards. So around 2001 - 2003, the splitting of OS and data for databases was already no longer the right approach. Physical or virtual, it's not been the thing to do for a long time.
-
@Grey said in SQL drive assignations:
When it was established as a best practice to do this, we were using physical machines and the reasoning, as I recall, was to separate the IO for SQL so that there was no contention with the OS.
It was because NTFS failure domains needed to be isolated and the OS was small and could fit on a highly reliable RAID 1 pair and the data was large and needed RAID 5 for capacity no matter how risky or slow it was. It was never a best practice, only a "common cost cutting measure as no one could afford RAID 10 back then."
-
@Grey said in SQL drive assignations:
Now, it's all going to the same bucket (SAN or DAS, but all the same VM datastore, unless you have different datastores dedicated to MDF/LDFs).
Important to note that we would still split things with VMs if it was important. But it is the opposite of what you are thinking. In the modern world we have one big bucket BECAUSE things changed before we had VMs, not because of VMs. The cause and effect are backwards from what you were expecting.
-
@Grey said in SQL drive assignations:
As such, is the thought behind using different drives still valid?
Partitioning can still make sense, it gives you some flexibility and control in limited cases. For the most part, you don't split your drives up very much because it's just more to manage. One Big RAID 10, one virtual disk for the OS, one for the data is normally plenty and if the DB is small, even that is often silly. Keep it simple.
-
@scottalanmiller said in SQL drive assignations:
@Grey said in SQL drive assignations:
As such, is the thought behind using different drives still valid?
Partitioning can still make sense, it gives you some flexibility and control in limited cases. For the most part, you don't split your drives up very much because it's just more to manage. One Big RAID 10, one virtual disk for the OS, one for the data is normally plenty and if the DB is small, even that is often silly. Keep it simple.
I would recommend a separate partition for the tempdb since it can grow and use up all the available space on a drive.
Depending on how you are handling backups and translogs, that may be a separate partition as well, but not necessarily a must.
-
Cool. Thanks for the input!
