Hyper-V and SQL Server Test Environment RAID vs NO RAID

[arcusfront]

We are running 20-25 different SQL Server test systems (SQL 2005, 2008, Windows 7, 2008, 2008 R2)in a Hyper-v 2012 environment for 4-6 concurrent users. Each user has their own separate hyper-v guest test systems, meaning none of the users share the same concurrent environment and not all of the test systems are being used at the same time.

As part of our testing, we often restore/backup multiple SQL databases per day. These databases can be anywhere from 100 MB to 8-10 GB in size and we also do testing with BCP and our own custom log shipping and whole database archiving system which can take an hour or more to run. Currently we are running in RAID 10 (4 SATA 3 drives) on a Dell T420 server with 64 GB and dual Xeon processors. Note that the underlying Hyper-V OS is on a separate non RAID drive .

Inevitably, when one person is restoring, doing backups, archiving of these large databases, disk I/O takes a performance hit for all users. As the T420 can accommodate more drives (8 total) would increasing the drives in the RAID help any with disk I/O

As this is just a test system and with no worries of mission critical systems running on it, can we do with out the RAID and assign each user their own separate physical drives (JBOD) on the server with their own hyper-v test systems)? ie: user1 drive d:, user2 drive e:, user3 drive 4, and so on. Would this help on disk I/O performance as each user is not reading/writing to the same raided drives?

We are not adverse to switching to SSD drives and or purchasing another server but that may not happen until mid to late 2014. Alternatively and in the meantime, I was thinking of offloading some of the Hyper-v guests to other under-utilized servers via the Hyper-v 2012 SMB feature.

Any other suggestions would be appreciated.

 

[k1pp3r]

Adding spindles (HDD) to the RAID set will help performance, but how many you need depends.

You should run a performance counted to figure out what your max IOPS are and go from there.

your typical 7200 RPM SATA drive will yeild you around 100 IOPS per disk in the raid set. If you moved up to 15K SAS drives you would get 175 IOPS per disk, but at a high price premium.

All in all, SQL should always sit on the fastest drives you can find (Rotational, not SSD), There are mixed opinions on putting SQL databases on SSD drives. I have not tested SQL with SSD drives yet.

You could also setup a NFS share where users write ther backups so they are not writing to the same RAID set that the DB's are running on.

Dell has an IOPS tool that you can load on your server to see what is going on, call them and ask for it, then run it for a week to see where you are.

 

[hardware_failure]

What are you reading and writing your backups from?

 

[arcusfront]

Adding spindles (HDD) to the RAID set will help performance, but how many you need depends.

You should run a performance counted to figure out what your max IOPS are and go from there.

your typical 7200 RPM SATA drive will yeild you around 100 IOPS per disk in the raid set. If you moved up to 15K SAS drives you would get 175 IOPS per disk, but at a high price premium.

All in all, SQL should always sit on the fastest drives you can find (Rotational, not SSD), There are mixed opinions on putting SQL databases on SSD drives. I have not tested SQL with SSD drives yet.

You could also setup a NFS share where users write ther backups so they are not writing to the same RAID set that the DB's are running on.

Dell has an IOPS tool that you can load on your server to see what is going on, call them and ask for it, then run it for a week to see where you are.

Thanks for the info. I'll look into contacting Dell next week in regards to the IOPS tool.

 

[arcusfront]

What are you reading and writing your backups from?

We have a separate file server where client SQL databases are stored in zip files. These zip files are copied to, unzipped and all reading/writing/testing etc are done within the specific hyper-v guest test system.

 

[peanuthead]

Why not give a disk to each user? Would be enough I/O for them?

 

[k1pp3r]

Why not give a disk to each user? Would be enough I/O for them?

You can't allocate disks like that in an array.

 

[arcusfront]

You can't allocate disks like that in an array.

As mentioned in my original post, we can do with out the RAID and assign each user their own separate physical drives. As this is just a test system, we can do without RAID redundancy if need be.


I also have .vhd templates that have all requisite software, updates etc installed then sysprep run. These are are all backed up to another server and can be easily copied over and deployed within minutes if we ever have to replace a physical drive or a hyper-v image gets corrupted.

 

[jimh425]

Are you sure it's disk io and not network io? Are you using fixed disks or thin provisioning? In general, more disks are better assuming the controller can keep up. Individual SSDs would probably be faster.

 

[peanuthead]

You can't allocate disks like that in an array.

I was stating pass the disk directly to the VM or just allocate the single disk to the VM via NFS, or CIFS, etc. You would have to break the array.

I deal with DB sizes from 50MB-several GB daily. A single user to a disk can be done. It's sufficient but not optimal.

 

[arcusfront]

Are you sure it's disk io and not network io? Are you using fixed disks or thin provisioning? In general, more disks are better assuming the controller can keep up. Individual SSDs would probably be faster.

Definitely not network related. The server has 2x Gigabit NIC's on board. One NIC is solely reserved for the physical host and the second NIC is for the virtual network. The whole physical network with multiple vlans is Gigabit with Cat6. The Dell server is plugged into a Cisco SG100-24 switch. Network throughput on the server and the entire physical network is not a problem.

We are using fixed disks.