ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Sizing a Server and Disks - SQL VM

    IT Discussion
    esxi host vmware sql server virtual machine
    11
    112
    9.9k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • hobbit666H
      hobbit666
      last edited by

      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

      scottalanmillerS 1 Reply Last reply Reply Quote 1
      • hobbit666H
        hobbit666
        last edited by

        Here is the requirements guide for GP from Microsoft
        https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-requirements/MDGP2018_System_Requirements

        1 Reply Last reply Reply Quote 0
        • DustinB3403D
          DustinB3403
          last edited by

          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.

          1 Reply Last reply Reply Quote 2
          • hobbit666H
            hobbit666
            last edited by

            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.

            black3dynamiteB DustinB3403D 2 Replies Last reply Reply Quote 0
            • DustinB3403D
              DustinB3403
              last edited by

              If you're using SSDs than OBR5 would be perfectly fine as well.

              hobbit666H 1 Reply Last reply Reply Quote 2
              • hobbit666H
                hobbit666 @DustinB3403
                last edited by

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

                DustinB3403D scottalanmillerS 2 Replies Last reply Reply Quote 0
                • DustinB3403D
                  DustinB3403
                  last edited by

                  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.

                  hobbit666H 1 Reply Last reply Reply Quote 0
                  • DustinB3403D
                    DustinB3403 @hobbit666
                    last edited by

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

                    JaredBuschJ 1 Reply Last reply Reply Quote 0
                    • black3dynamiteB
                      black3dynamite @hobbit666
                      last edited by black3dynamite

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

                      1 Reply Last reply Reply Quote 2
                      • DustinB3403D
                        DustinB3403 @hobbit666
                        last edited by

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

                        1 Reply Last reply Reply Quote 2
                        • dafyreD
                          dafyre
                          last edited by dafyre

                          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

                          DustinB3403D scottalanmillerS 2 Replies Last reply Reply Quote -1
                          • DustinB3403D
                            DustinB3403 @dafyre
                            last edited by

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

                            DashrenderD 1 Reply Last reply Reply Quote 3
                            • DashrenderD
                              Dashrender @DustinB3403
                              last edited by

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

                              DustinB3403D 1 Reply Last reply Reply Quote 0
                              • DashrenderD
                                Dashrender
                                last edited by

                                I’d say a single VMDK would be less complex than 3-4 VMDK attached.

                                DustinB3403D 1 Reply Last reply Reply Quote 0
                                • DustinB3403D
                                  DustinB3403 @Dashrender
                                  last edited by

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

                                  1 Reply Last reply Reply Quote 0
                                  • DashrenderD
                                    Dashrender
                                    last edited by

                                    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

                                    DustinB3403D 1 Reply Last reply Reply Quote 0
                                    • DustinB3403D
                                      DustinB3403 @Dashrender
                                      last edited by

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

                                      DashrenderD 1 Reply Last reply Reply Quote 1
                                      • DashrenderD
                                        Dashrender @DustinB3403
                                        last edited by

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

                                        dafyreD 1 Reply Last reply Reply Quote 0
                                        • scottalanmillerS
                                          scottalanmiller @hobbit666
                                          last edited by

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

                                          hobbit666H 1 Reply Last reply Reply Quote 1
                                          • DustinB3403D
                                            DustinB3403 @Dashrender
                                            last edited by

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

                                            1 Reply Last reply Reply Quote 1
                                            • 1
                                            • 2
                                            • 3
                                            • 4
                                            • 5
                                            • 6
                                            • 1 / 6
                                            • First post
                                              Last post