Sizing a Server and Disks - SQL VM
-
Term was used loosely before 2012, but was solidified in this 2012 article: OBR10 A New Standard in Server Storage.
-
@scottalanmiller Much appreciated.
-
@aidan_walsh said in Sizing a Server and Disks - SQL VM:
@scottalanmiller Much appreciated.
You almost never see it written as OBR, it's normally written like "Hey, I'd recommend either OBR10 or OBR6 for your situation." So searching on OBR alone wouldn't turn up much.
It had to be written all of the time because people would recommend, say RAID 6, but people would interpret that as "multiple arrays, one of them being RAID 6", which isn't the same thing.
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
Should of mentioned here on the first post we currently run a IPOD. So when I run a DPACK do I run it on just the 3 host servers? Or on the servers and the SAN?
*Edit was planning on running on over a week time span soon.I'm not sure if you can run it inside the hypervisor, but you can definitely run it inside every VM, then you know on a VM by VM basis what that VM is doing.
-
@dashrender depends on the hypervisor, ESXi is supported, you simply need admin access to the host.
I wouldn't be surprised if Hyper-V works too.
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST. - MS SQL VM: (virtual disk (os drive letter))
-
@dustinb3403 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:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
So what the OP needs to do is get IOPs requirements of his environment, and build toward that.
This is where I hold my hands up. I have no idea when to measure this, as in what we use now, and how to calculate what we need and for future.
Just run a Dell DPACK scan for 3 or 4 days against your servers. You don't want to measure something at just one specific time as you wouldn't get a real view of the results.
The longer the better. For example, some companies have a process that only runs monthly, so if you're not running DPACK at that time, you could miss a high load time.
This is true of course. I was just saying as a base to get an idea of what he uses.
I ran a dpack for a week and our IOPS are insanely low here.
To prepare for an hardware upgrade, does one run DPACK on the VMs, the Hypervisor or both?
-
@black3dynamite 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:
@dustinb3403 said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
So what the OP needs to do is get IOPs requirements of his environment, and build toward that.
This is where I hold my hands up. I have no idea when to measure this, as in what we use now, and how to calculate what we need and for future.
Just run a Dell DPACK scan for 3 or 4 days against your servers. You don't want to measure something at just one specific time as you wouldn't get a real view of the results.
The longer the better. For example, some companies have a process that only runs monthly, so if you're not running DPACK at that time, you could miss a high load time.
This is true of course. I was just saying as a base to get an idea of what he uses.
I ran a dpack for a week and our IOPS are insanely low here.
To prepare for an hardware upgrade, does one run DPACK on the VMs, the Hypervisor or both?
I ran on the hypervisor, I didn't need to go so granular and look a the VM's (now sql or heavy systems on premise).
This is entirely up to you though.
-
@tim_g more how many disk in each RAID.
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g more how many disk in each RAID.
10 SSDs
6 HDDs -
Right Monday I'll setup a DPACK and run it for a week. See what I get from that.
-
@tim_g said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST.What is your C : Drive?
- MS SQL VM: (virtual disk (os drive letter))
-
@dashrender said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST.What is your
Drive?The smile drive is where you store the memes.
- MS SQL VM: (virtual disk (os drive letter))
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g more how many disk in each RAID.
Again, this is completely situation based.
I see Tim answered for his setup, but it will have zero relation to your needs.
-
@scottalanmiller said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST.What is your
Drive?The smile drive is where you store the memes.
/sigh - it displace the letter c followed by colon as
again /sigh. - MS SQL VM: (virtual disk (os drive letter))
-
@dashrender said in Sizing a Server and Disks - SQL VM:
@scottalanmiller said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST.What is your
Drive?The smile drive is where you store the memes.
/sigh - it displace the letter c followed by colon as
again /sigh.I got caught with that too. I need to remember to use C:\ instead.
- MS SQL VM: (virtual disk (os drive letter))
-
@dashrender said in Sizing a Server and Disks - SQL VM:
@scottalanmiller said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST.What is your
Drive?The smile drive is where you store the memes.
/sigh - it displace the letter c followed by colon as
again /sigh.Just don't displace your colon!
- MS SQL VM: (virtual disk (os drive letter))
-
@scottalanmiller said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@scottalanmiller said in Sizing a Server and Disks - SQL VM:
@dashrender said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
@hobbit666 said in Sizing a Server and Disks - SQL VM:
@tim_g said in Sizing a Server and Disks - SQL VM:
We have a Hyper-V host with two tiers of storage: an all SSD RAID, and an all HDD RAID.
WHen I set up the MS SQL server (mainly for MS Dynamics purposes, but it also serves some other critical business functions), I had to do it according to what the Dynamics consultant suggested:
- MS SQL VM: (virtual disk (os drive letter))
- serv-SQL.vhdx (C:)
- serv-SQL-DATA.vhdx (D:)
- serv-SQL-LOG.vhdx (E:)
- serv-SQL-BACKUP.vhdx (F:)
The D and E virtual disks are located on the SSD RAID on the physical host, the other two are on the HDD RAID.
The stuff needing to be fast like the TempDB, Log, main DB, etc is all on SSD... while the backups and the OS are on the HDD RAID.
I do have SSD caching for the HDD RAID, so the other stuff is actually sped up, though not 100% of the time. Most writes are anyways.
How have you got you disks split? Is it 50/50 SSD/Spinning?
I'm not sure what you're asking.
But the physical Hyper-V Host simply has two physical RAIDs.
- HOST
- 1st RAID (which is a RAID10)
- SSDs
- 2nd RAID (also a RAID10)
- HDDs
- 1st RAID (which is a RAID10)
These RAIDs are completely separate. On the HOST, the SSD RAID is the
D:drive, and the HDD RAID is theE:drive. I store the virtual machine virtual disks (the .vhdx disks) on the one it needs to go on depending on IOPS requirements. For example, the database and log virtual disks of MS SQL virtual machines go on theD:drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on theE:drive of the HOST.What is your
Drive?The smile drive is where you store the memes.
/sigh - it displace the letter c followed by colon as
again /sigh.Just don't displace your colon!
Keep it clean!
- MS SQL VM: (virtual disk (os drive letter))
-
Running a 7 day DPACK (now liveoptics)
-
@hobbit666 said in Sizing a Server and Disks - SQL VM:
Running a 7 day DPACK (now liveoptics)
Have fun, see you next week
