VMDK or iSCSI NTFS for SQL volume greater than 2TB?

[jwolf81]

I have some SQL 2012 data volumes that will be growing over 2TB in space, and some may grow up to 40TB in space in the coming months. My question is what is the best way to build these volumes? Right now we are using one 2TB VMDK formatted in vmfs5, but i will need to start expanding the drive size quickly.

We do not want to have to manually break up the data files in SQL, so one volume presented to the OS is ideal. Right now I see my options as such:

• Build a 40TB volume as and present it through VMware as a physical RDM and format NTFS
• Build separate 2TB VMFS volumes as vmdk disks and expand the volume in Windows by 2TB each time I need to add space
• Build 40TB volume and present it to Windows using the Server 2k8 iSCSI drivers and format NTFS

Performance is the number one priority for these volumes, with management coming in next priority-wise. Do any of these options provide substantial performance increases over the others? All being equal, my preference is the second option to just expand by 2TB when i need to add space. My intuition tells me to prefer the third option over the RDM.

Thoughts?

[nick_furnell · Accepted solution]

Hi Jacob

I don't have experience of volumes as large as you suggest, but I have just suffered the consequences of NOT planning ahead myself! I have just faced an issue with a Windows volume hitting 2TB ( I have an article here explaining what I did and why).

I think that the best method is Option 3, as the Nimble will allow the volume to be presented using iSCSI and you can take advantage of the MPIO options for redundancy across the 4 NIC’s.

As for performance, I don’t know of any differences between the options you suggest – but I would expect that iSCSI presentation directly from the Nimble would be the easiest to manage. You can also implement Hardware or VSS snapshots.

Nick

[hduong71]

Wow! Looks like some growth you have their Jacob!

My recommendation is to use the iSCSI attached within the guest OS if performance is your goal (option #3). The multipath I/O handling within Windows is a lot better (Least Queue Depth) and will get you the best performance.

Second, If you ever have to recover the database with the Nimble snapshots, it's a lot easier since you don't have to deal with VMware in the middle. Simply create a clone of the snapshot and present it back up to the Windows VM.

Some Gotchas in setting iSCSI attached up:

1. Make sure you separate your data and log volumes out if possible.

2. When you format the NTFS volume, format it with 64KB.

The only reason I would ever see you use RDM is if you have plans on using SRM with VMWARE, however you can still accomplish SRM with Iscsi attached within Windows with some scripting.

Hope this helps

--Huy

[#4]

I also vote for number 3.  There is next to no noticeable performance gain from going ISCSI within guest vs RDM and with RDM's you have to decide virtual or physical RDM each with their own trade-offs.

Just a couple additional points to be aware of:

1.  When you configured your Virtual Machine ports for the iSCSI guest network be sure to duplicate it exactly on all nodes in the cluster so you don't have any vMotion/DRS issues.  Some people just add virtual machine ports to the current vSwitch that's also running VMkernel ports for iSCSI, or if you have the extra physical NICs per host available, create a dedicated vSwitch to iSCSI guest traffic.  There isn't much in the way of documented performance gains in either switch config, it's a matter of preference and network port availability.

2.  Yes you should definitely separate logs and database LUNs, but ALSO separate your system DB's from your user DB's (Master, model, tempDB) This way, if you do leverage Nimble snapshots, your user database snapshot schedule doesn't cause any issues with system DB's snapshots.  (it's usually ok to combine system DB's and Logs on the same drive) Most DBA's prefer regular SQL backups for system DB's once a day as part of their maintenance plans.  Also, depending on how many add/delete/inserts take place on your user databases, some people also dedicate a LUN just for tempDB.  Since you're already targeting DB's around 40TB, I'd consider this as well.  The benefit of having your databases LUN's broken out is two-fold.  One, you can monitor where your IOPS are going (system DB's, user DB's, tempDB, etc) Two, you can increase capacity only where you need it and you can assign the proper Nimble performance policy appropriately per LUN.

Hope that help!

Tony

[jwolf81]

FWIW: In testing the Windows iSCSI configuration, I've seen about a 20% decrease in performance when running identical sets of data through IOmeter. I built a small volume, added a new set of 2 VM port groups and added 2 vnics to one of our SQL servers. I then installed the Nimble Windows Integration kit and ran through the Windows MPIO setup with Least Queue Depth as the load balancing option.

I can definitely see pretty even traffic going over both nics from both the OS and the Nimble metrics, and I'm showing four connections to the volume from the Nimble side. I know my data set isn't larger than 10 GB, so I'm getting 100% cache hit rate on the volumes. I don't seem to be saturating the network either, but I'm losing about 20% of my IOPS.

I haven't spent a ton of time tuning the nics or messing with flow control, jumbo frames, etc on the new iSCSI network, but it's definitely a surprise to see the difference.

[mprzybylek49]

Which side is performing better?  The volumes connected as VMDKs or as iSCSI within the guest?

[hduong71]

Hi Jacob,

That's really strange, according to VMWARE those 3 options should give you very simililar performance.

Are you using the VMware VMXNET NICs for the VM and not the e1000?

--Huy

[jwolf81]

The vmfs volume is performing better.

Yes, I'm using vmxnet3 on each of the two nics. The only other adjustment I've made to the nics is disabling ipv6.

The volume in Windows is formatted NTFS with 64K allocation unit size. On the storage array, I'm using the SQL Server 2012 performance policy (8k blocks IIRC).

[hduong71]

Hi Jacob,

I would give a call to our support guys at Nimble.

According to VMWARE there shouldn't be that much of a performance difference.

I also found this article that is really good in describing your options.

Virtualized Exchange Storage: VMDK or RDM or…? | Virtualize Business Critical Applications - VMware Blogs