Sizing a Server and Disks - SQL VM

[hobbit666]

Right Monday I'll setup a DPACK and run it for a week. See what I get from that.

[Dashrender]

@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

These RAIDs are completely separate. On the HOST, the SSD RAID is the D: drive, and the HDD RAID is the E: 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 the D: drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on the E: drive of the HOST.

What is your C : Drive?

[scottalanmiller]

@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

These RAIDs are completely separate. On the HOST, the SSD RAID is the D: drive, and the HDD RAID is the E: 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 the D: drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on the E: drive of the HOST.

What is your Drive?

The smile drive is where you store the memes.

[Dashrender]

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

[Dashrender]

@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

These RAIDs are completely separate. On the HOST, the SSD RAID is the D: drive, and the HDD RAID is the E: 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 the D: drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on the E: 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.

[black3dynamite]

@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

These RAIDs are completely separate. On the HOST, the SSD RAID is the D: drive, and the HDD RAID is the E: 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 the D: drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on the E: 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.

[scottalanmiller]

@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

These RAIDs are completely separate. On the HOST, the SSD RAID is the D: drive, and the HDD RAID is the E: 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 the D: drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on the E: 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!

[Obsolesce]

@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

These RAIDs are completely separate. On the HOST, the SSD RAID is the D: drive, and the HDD RAID is the E: 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 the D: drive of the HOST, and the OS and Backup .vhdx virtual disks of that virtual machine go on the E: 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!

[hobbit666]

Running a 7 day DPACK (now liveoptics)

[scottalanmiller]

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

Running a 7 day DPACK (now liveoptics)

Have fun, see you next week

[hobbit666]

Had to stop the DPACK and re-run.

Hopefully results tomorrow afternoon

[hobbit666]

OK so are you ready for Slide show city here comes the graphs

Basic Layout of the 3-2-1

[hobbit666]

Page1

[hobbit666]

Page2

[hobbit666]

Page9

[hobbit666]

Page10

[hobbit666]

Page11

[hobbit666]

Page13-15

[hobbit666]

Page5 - ESXi Host 3 - This one only runs the SQL VM

[hobbit666]

Page6 - ESXi Host 2