SQL Server 2005 Best Practices for creating virtual disks

[JWC]

I searched through the forum and can't seem to get a rock solid answer on a relatively simple question on what the best practice is for the creation of virtual disks for a SQL Server 2005 installation on VI3. M$ recommends the separation of TempDB, Data, Transaction Logs onto different spindles for performance reasons and initially I carried over the recommendation from a physical server to a virtual server. I'm not entirely sure that this results in a performance benefit in the virtual world though.

We are setup on a SAN LUN that is RAID5 and I placed the SQL Sever 2005 VM and all files that make up the VM on that one LUN. I have four virtual disks (c:\OS, d:\Data, e:\Logs, f:\TempDB). Anyone know if in this setup, does the separation on to different virtual hard disks result in any performance gains or can I get away with say two hard disks (c:\OS, d:\Data, d:\Logs, f:\TempDB)? Will the performance differ? I also have other VMs on the same LUN.

I've also read that having additional CPUs may result in performance gains related to handling disk I/O to the virtual disks, but does this matter if your underlying physical disk are on a SAN LUN in a RAID 5 configuration?

How are others handling setting up SQL on virtual machines?

Thanks!

[jygoman]

Log files and data files for MS-SQL2k5 should be on a RAID10 virtual disk. Depending how much usage your database will be hit traditionally RAID5 is not good at fast write IO operations. So rather RAID 5 is okay for the OS and RAID 10 for the data files. Best way to gauge the performance difference is using the adaptive scheme especially if you have other vm's in that datastore. I would create new a new lun and configure it for RAID10 and transfer the log\data files over and see what the performance gain would be.

[JWC]

jygorman,

Thanks for the tip, I'll have to do some testing in our lab environment. I will post updates when I get some results back from my testing.

[RobBuxton]

The answer can also be influenced by the SAN used as well.

Some SAN's (e.g. HP's EVA series) split a LUN over all of the disks in the SAN. So the need to separate log files etc. onto a different LUN is not valid as a performance requirement.

It can also depend on the size of the LUN and whether you want to keep it all in the VMFS or, for a large LUN make it an separate RDM device.

You also need to take into account how busy this server is.

The general recommendation is to not have multi-CPU VMs unless the application really does benefit from it as there are some constraints around multi CPU guests. If you search around you'll get some good insight into it.

Our SQL Servers have a single LUN with logs, databases etc. on it all within the VMFs. But then they're neither that busy or that large.

As they say, your mileage may vary.

[mitchellm3]

Following is how we set up our larger SQL servers.

OS - .vmdk

Data - RDM

Logs - RDM

Backup/dump area - RDM

We use R5 Arrays. Each array is usually 7+1. Therefore the server is spread across 32 spindles...granted 8 are for sql dumps. In the case of a smaller SQL server, we don't use RDMs but we still spread the disks across multiple data stores (still 32 spindles). We also try to use one CPU unless the vendor cries for two. We did find that adding a second CPU helped greatly in the case where an app was also installed on a SQL server. Usually if the app got busy, it and SQL would compete for the 1st cpu. All we had to do was peg SQL to the second CPU and voila!

Our thought process:

1. Spread the server across as many spindles as possible

2. with RDMs, you don't have as much contention for the LUN. Only that server gets access to it, not the other 20 VMs that are on the LUN.

One new thing learned at VMworld that we started doing. The VM engineer said that they have seen much better I/O performance when using multiple drives AND putting each drive on its own SCSI controller. For example, instead of having our SCSI layout as 0:0, 0:1, 0:2, 0:3, it is now 0:0, 1:0, 2:0, 0:1. Now you may wonder why we didn't do 3:0, well we use 2 nics in every server for a live and backup network, so we are limited to 3 SCSI controllers.

As it was stated before, it does depend on the type of SAN you have. We use IBM DS series arrays. So, we have to plan each server to maximize spindles. Soon, it will change cause we are getting IBMs San Volume Controller which will spread each lun aross an entire storage array. Soon we will spreading servers across multiple storage arrays....hundreds of spindles...pretty cool stuff.

[JWC]

Thanks for sharing some of your insight into the matter mitchellm3, it's helpful to get feedback from individuals that are actually putting into practice a certain methodology.

We use EMC DMX for our production servers and Clarion for our dev/test servers. I am working with 250GB LUNs with 6 to 7 VMs running per LUN. I'm not sure how many disks are in the RAID5 array as we are not the SAN administrators. I'm trying to stay away from using RDMs just for the simple fact that we are using VCB to backup entire VMs. I've also read that there is no performance benefit to using RDMs in lew of vmdks. Our SQL boxes are dedicated to only running SQL but some run different Instances on the same box. Our applications run on separate vms.

I'll try using different SCSI controllers for the different vmdk files and see if it improves performance. It's easy enuf to test. We only run one NIC per vm so I can use the 0:0, 1:0, 2:0, 3:0 scheme for our vmdks. Do you forsee any benefit in doing this using all vmdk disks?

[mitchellm3]

No problem. Glad I can help. As far as RDMs not giving any performance advantages over .vmdk files...I agree. BUT...let me clarify. We use RDMs (virtual mode only) for 3 reasons:

1. The VM that is attached to the RDM has sole access to it. Therfore you don't have 20 systems hammering on one LUN...just one.

2. Since an RDM is its own LUN, I can expand it without concern of filling up a VMFS volume. We use 350G data stores, the majority of our VMs fit nicely into them. Not that VMFS volumes can't be extended by extents but I'm not going down that road.

3. In the beginning of virtualization at our company, we wanted the ability to attach a physical machine directly to the data of our bigger VMs in case of a "virtual disaster" but that isn't important anymore. Virtualization is key to our DR strategy.

As far as seeing benefit using different scsi controllers using vmdk disks? Yes, it should perform better...if the VM engineer wasn't just blowing smoke. We are using this with all new VMs...whether or not they use RDMs...of which only the big ones do.

Anyway, good luck.

btw, i believe vcb works with RDMs. But just to confirm I'm running a test right now.