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.
    0_1516883720135_Single.png

    Single Raid10 - Separate VMDK for each drive on the VM
    0_1516883693898_Single-Multi.png

    Multiple RAIDS for different VMDK for VM

    0_1516883698160_Multi.png





  • 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 TempDB

    Why 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 TempDB

    Why 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 TempDB

    Why 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 other

    Windows 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 TempDB

    Definitely 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?



  • @dustinb3403 said in Sizing a Server and Disks - SQL VM:

    @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.

    WTF are you talking about here, no where is he talking about arrays. You are injecting pure shit.

    An array may only go as fast as the slowest drive in the array, but he was talking about an array with SSD and a different array with SAS.



  • @jaredbusch said in Sizing a Server and Disks - SQL VM:

    @dustinb3403 said in Sizing a Server and Disks - SQL VM:

    @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.

    WTF are you talking about here, no where is he talking about arrays. You are injecting pure shit.

    An array may only go as fast as the slowest drive in the array, but he was talking about an array with SSD and a different array with SAS.

    Clearly you didn't read the topic.



  • @dustinb3403 said in Sizing a Server and Disks - SQL VM:

    @jaredbusch said in Sizing a Server and Disks - SQL VM:

    @dustinb3403 said in Sizing a Server and Disks - SQL VM:

    @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.

    WTF are you talking about here, no where is he talking about arrays. You are injecting pure shit.

    An array may only go as fast as the slowest drive in the array, but he was talking about an array with SSD and a different array with SAS.

    Clearly you didn't read the topic.

    No, he is talking about tiering data in an earlier post. That would mean having an array for SSDs and an array for SAS HDD.



  • @dustinb3403 said in Sizing a Server and Disks - SQL VM:

    @jaredbusch said in Sizing a Server and Disks - SQL VM:

    @dustinb3403 said in Sizing a Server and Disks - SQL VM:

    @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.

    WTF are you talking about here, no where is he talking about arrays. You are injecting pure shit.

    An array may only go as fast as the slowest drive in the array, but he was talking about an array with SSD and a different array with SAS.

    Clearly you didn't read the topic.

    Clearly I did. FYI, his post was post 6 that you quoted, and I quoted your post that was post 8.

    You injected this idiototic statement of mixing drives in an array when the OP was clearly just looking at what drives to get to create his arrays on.