Sizing a Server and Disks - SQL VM
-
Looking for some "best practice" guides on Virtual Machines and SQL Server.
Server Usage - 5-6 VMs, DC, Finance, Misc, SQL/DynamicsGP2018 ..........
DynamicsGP - Est 500GB SQL Database size, 100000 lines being written to the SQL per day.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.
Or do you split the 12 disks into smaller Raids for different VMDKs/Datastores for the different drives in the VM?Hopefully this Visual guide on my thinking will explain
Single Raid10 - Single VMDK - Partitioned within the VM.
Single Raid10 - Separate VMDK for each drive on the VM
Multiple RAIDS for different VMDK for VM
-
Here is the requirements guide for GP from Microsoft
https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-requirements/MDGP2018_System_Requirements -
ONE BIG RAID 10.
It's faster and more reliable and less stress / work the controller would have to do for multiple arrays.
Splitting arrays was done because it HAD to be done way back when. It wasn't because it was good to do.
Install the hypervisor directly onto the array, and then use it's installation process to setup on host storage for your VM's.
-
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. -
If you're using SSDs than OBR5 would be perfectly fine as well.
-
@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? -
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.
