Sizing a Server and Disks - SQL VM
-
FYI nothing in your OP states the type of drives so we have to make an assumption based on the drawings.
But if you are using SSDs, unless you need some really insane IOPS, use OBR5, you get more storage and it is more than reliable enough.
If using HDDs use RAID10.
Obviously all of the conditions apply with both (RAID 5 ssd) don't use consumer gear, enable monitoring, replace equipment when it fails etc etc.
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
If you're using SSDs than OBR5 would be perfectly fine as well.
Depends on price, would it be acceptable to have X SSD's just for the SQL VM and then the rest SAS for all other VMs?
Or would you just go for fill the server with SSD/SAS drives?No, don't mix the drive types. As the array will only go as fast as the slowest drive anyways.
If you need a RAID Cache setup a few SSDs for that purpose, but don't mix.
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
So basically Picture one.
One big RAID with 12 SSD's
- Setup the SQL VM and partition withing the VM as if it was a physical server so OS 
Logs E: TempDB etc.Instead of partitioning, just use multiple vmdk.
vmdk1 = OS
vmdk2 = Logs
vmdk3 = TempDB
... -
@hobbit666 said in Sizing a Server and Disks - SQL VM:
So basically Picture one.
One big RAID with 12 SSD's - Setup the SQL VM and partition withing the VM as if it was a physical server so OS Logs E: TempDB etc.@black3dynamite said in Sizing a Server and Disks - SQL VM:
Instead of partitioning, just use multiple vmdk.
vmdk1 = C OS
vmdk2 = D Logs
vmdk3 = E TempDB
...What @black3dynamite is saying is just create more virtual hard drives and attach them to the VM, and let Windows or whatever OS manage it how it wants.
Don't create 1 massive virtual disk and then partition it (this simply adds complexity when there is no need)
-
For the host, OBR10 almost always. If you have an exception to this rule of thumb you'd know it (somebody here would likely say it, ha ha).
I would also do one big VMDKfor the SQL server VM, and partition the disk.... Use a 2TB disk (just throwing a number out there)...
256 GB = C:\ -- OS / Applications
1024 GB = D:\ -- SQL Server Data
512 GB = E:\ -- SQL Translogs / BAK files
256GB = F:\ -- SQL TempDB -
@dafyre said in Sizing a Server and Disks - SQL VM:
For the host, OBR10 almost always. If you have an exception to this rule of thumb you'd know it (somebody here would likely say it, ha ha).
I would also do one big VMDKfor the SQL server VM, and partition the disk.... Use a 2TB disk (just throwing a number out there)...
256 GB = C:\ -- OS / Applications
1024 GB = D:\ -- SQL Server Data
512 GB = E:\ -- SQL Translogs / BAK files
256GB = F:\ -- SQL TempDBWhy would you partition inside of the VM? That is adding complexity for no obvious gain. Simply create different drives, this way you can scale up each drive as needed.
-
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@dafyre said in Sizing a Server and Disks - SQL VM:
For the host, OBR10 almost always. If you have an exception to this rule of thumb you'd know it (somebody here would likely say it, ha ha).
I would also do one big VMDKfor the SQL server VM, and partition the disk.... Use a 2TB disk (just throwing a number out there)...
256 GB = C:\ -- OS / Applications
1024 GB = D:\ -- SQL Server Data
512 GB = E:\ -- SQL Translogs / BAK files
256GB = F:\ -- SQL TempDBWhy would you partition inside of the VM? That is adding complexity for no obvious gain. Simply create different drives, this way you can scale up each drive as needed.
Where is this extra complexity you speak of coming from?
-
I’d say a single VMDK would be less complex than 3-4 VMDK attached.
-
@dashrender said in Sizing a Server and Disks - SQL VM:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@dafyre said in Sizing a Server and Disks - SQL VM:
For the host, OBR10 almost always. If you have an exception to this rule of thumb you'd know it (somebody here would likely say it, ha ha).
I would also do one big VMDKfor the SQL server VM, and partition the disk.... Use a 2TB disk (just throwing a number out there)...
256 GB = C:\ -- OS / Applications
1024 GB = D:\ -- SQL Server Data
512 GB = E:\ -- SQL Translogs / BAK files
256GB = F:\ -- SQL TempDBWhy would you partition inside of the VM? That is adding complexity for no obvious gain. Simply create different drives, this way you can scale up each drive as needed.
Where is this extra complexity you speak of coming from?
Allowing Windows to manage things is the complexity
. Just let the hypervisor manage the hardware that each VM has, and let the guest simply use what is there. -
You’re robbing Peter to pay Paul.
You either manager in windows are you managing at the hypervisor. I haven’t seen an explanation as to why one would be better than the other -
@dashrender said in Sizing a Server and Disks - SQL VM:
I’d say a single VMDK would be less complex than 3-4 VMDK attached.
And adjusting the size of the VMDKs from within the OS will be a pain in the ass if you have to. Changing the size of a physical drive is stupidly simple otherwise.
Power off, increase upwards and then rescale from within the VM (without having to worry about overwriting anything).
-
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
I’d say a single VMDK would be less complex than 3-4 VMDK attached.
And adjusting the size of the VMDKs from within the OS will be a pain in the ass if you have to. Changing the size of a physical drive is stupidly simple otherwise.
Power off, increase upwards and then rescale from within the VM (without having to worry about overwriting anything).
Ok there is a bit of value here, but I’m pretty sure wcool modes will let you grow all disks other than the C drive as well. What I don’t know is if you have multiple partitions, in a single drive, will it grow one not next to the free space?
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
i.e. I see on the dynamics guides they recommend separate RAIDS for OS, Logs, TempDB and Data.
But is this just separate VMDK's (if for example we use ESXi) or should we say in a Server with 12 x 2.5" HD - have it all as one big RAID10.Separate VMDKs is never separate RAIDs. They are recommending different arrays for each.
They are wrong and this is ridiculously horrible guidance, but that is what they mean. What you are seeing is a 1990's guide regurgitated by someone non-technical who parroted back "rule of thumb" based on the assumption of using spinning disks, with RAID 5, without cache - basically, a run of the mill, physical, 1998 install.
Whatever guide this is, it's not for any product in the real world for nearly two decades.
-
@dashrender said in Sizing a Server and Disks - SQL VM:
You’re robbing Peter to pay Paul.
You either manager in windows are you managing at the hypervisor. I haven’t seen an explanation as to why one would be better than the otherWindows partition manager has (in my experience) proven to be unreliable while moving / changing partition sizes. Therefor my recommendation is just create separate disks, and if you need scale up a singular partition at a time, rather than trying to move 3 or 4 partitions and resizing everything in one go.
-
@dashrender said in Sizing a Server and Disks - SQL VM:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
I’d say a single VMDK would be less complex than 3-4 VMDK attached.
And adjusting the size of the VMDKs from within the OS will be a pain in the ass if you have to. Changing the size of a physical drive is stupidly simple otherwise.
Power off, increase upwards and then rescale from within the VM (without having to worry about overwriting anything).
Ok there is a bit of value here, but I’m pretty sure wcool modes will let you grow all disks other than the C drive as well. What I don’t know is if you have multiple partitions, in a single drive, will it grow one not next to the free space?
@DustinB3403 does shave a good point here. Because, no, in my experience, windows will not let you grow a partition that is not next to the free space. (It's been a while since I've had to do this).
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
If you're using SSDs than OBR5 would be perfectly fine as well.
Depends on price, would it be acceptable to have X SSD's just for the SQL VM and then the rest SAS for all other VMs?
Or would you just go for fill the server with SSD/SAS drives?Acceptable, yes. But does it make sense to get all those extra drives to be slower? It would have to save a bit of money to justify it.
-
@dafyre said in Sizing a Server and Disks - SQL VM:
For the host, OBR10 almost always. If you have an exception to this rule of thumb you'd know it (somebody here would likely say it, ha ha).
I would also do one big VMDKfor the SQL server VM, and partition the disk.... Use a 2TB disk (just throwing a number out there)...
256 GB = C:\ -- OS / Applications
1024 GB = D:\ -- SQL Server Data
512 GB = E:\ -- SQL Translogs / BAK files
256GB = F:\ -- SQL TempDBDefinitely not. You should "never" partition today. If you want partitions, that means that you actually wanted volumes. Partitions are effectively a dead technology - an "after the fact" kludge that exists for cases where voluming wasn't an option - which should never be the case today as this is solved universally. Partitions are fragile and difficult to manage and have many fewer options and less flexibility. They have no benefits, which is why they are a dead technology.
Partitions exist today only for physical Windows installs, where there is no hypervisor and no enterprise volume manager to do the work - in essence, they are for "never".
-
@dafyre said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
I’d say a single VMDK would be less complex than 3-4 VMDK attached.
And adjusting the size of the VMDKs from within the OS will be a pain in the ass if you have to. Changing the size of a physical drive is stupidly simple otherwise.
Power off, increase upwards and then rescale from within the VM (without having to worry about overwriting anything).
Ok there is a bit of value here, but I’m pretty sure wcool modes will let you grow all disks other than the C drive as well. What I don’t know is if you have multiple partitions, in a single drive, will it grow one not next to the free space?
@DustinB3403 does shave a good point here. Because, no, in my experience, windows will not let you grow a partition that is not next to the free space. (It's been a while since I've had to do this).
Also, if you use multiple volumes you can see them at all levels, rather than having them hidden, you can move them to different RAID arrays in the future as needed as simply as just moving them, you can snapshot them independently, back them up independently, etc.
-
@dafyre said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
I’d say a single VMDK would be less complex than 3-4 VMDK attached.
And adjusting the size of the VMDKs from within the OS will be a pain in the ass if you have to. Changing the size of a physical drive is stupidly simple otherwise.
Power off, increase upwards and then rescale from within the VM (without having to worry about overwriting anything).
Ok there is a bit of value here, but I’m pretty sure wcool modes will let you grow all disks other than the C drive as well. What I don’t know is if you have multiple partitions, in a single drive, will it grow one not next to the free space?
@DustinB3403 does shave a good point here. Because, no, in my experience, windows will not let you grow a partition that is not next to the free space. (It's been a while since I've had to do this).
Which this is the biggest issue. Moving partitions (generally is just a bad idea at least on Windows). With individual volumes though, you simply expand the partition into the newly available free space and it just works.
So many benefits to individual disks rather than a singular disk and multiple partitions (especially on windows)
-
@hobbit666 does that answer all of your best practice questions?
