SpringTownDBA (7/25/2012)
YSLGuru (7/25/2012)
Thanks ...Thanks for the feedback.
What does it mean that "only the OS would be on the VM and the database files would be on local drives"? I'm guessing that's as opposed to
A large percentage of system/san admins don't really understand how SQL server uses resources (cpu, ram, io) , and the ramifications of using VM's for sql server.
Sysadmins like VM's because they can oversubscribe the resources, but is a bad thing for high load SQL servers.
For an OLTP system, you might be best served with as much ram as you can shove into the machine, and one big RAID 10 array (like 20+ drives) for all your db files.
Unless you know for sure that your IO load is too high for one big raid 10 array, then I wouldn't bother getting fancy with multiple volumes. However, if you know that tempdb usage will be very high, then local SSD storage makes a lot of sense.
For a SAN, ensuring that caching is enabled, and set to 0% read/100% write caching for sql server can do more for write performance then raid5 vs raid10 or separate volumes for tempdb (not always, of course).
In order to work with system administrators about IO requirements of SQL Server successfully, you need to talk in terms of IOPS. Calculate how many you need, then test whatever server you're given to determine if you're actually getting them. If they can deliver your IOPS, then it doesn't matter what raid level it uses or how it's split up. If they can't deliver those IOPS (very likely in the first pass) then you can troubleshoot it from there. If you need an large amount of iops (i.e. more than one HBA can provide) then you'll need to get very very friendly with your SAN admin.
As for a VM vs physical HW, you need to understand how balloon drivers work, how sql server memory allocations work, how sql server gives memory back to the os when requested, min/max memory settings, the pitfalls of over subscribing ram among several vm's, etc. Then you can argue for or against.
"What does it mean that "only the OS would be on the VM and the database files would be on local drives"?
Its my understanding that the Windows OS will run inside a VMware session (or whatever its called) which means that any application installed will also run within that same VM. I'm told that this VM session is the only one that will run on the host server (the new server we're getting) and so the VM session will get %100 of the resources as opposed to sharing with other VM sessions. The OS drive is C. The other drives are locally attached RAID drives (as opposed to SAN based which is how the current server is setup).
I fought against moving to the use of VMware or anything else virtualized because I had read about issues with high load RDBMS on VM solutions even when the VM is properly configured for use with SQL Server. Originally the plan was to do everything within VMware and use SANs based drives. Using Red-Gates SQL Monitor I was able to show why this was a very bad idea since our current I/O is terrible because of our SANS setup which needs updating/replacing. The Avg Read/Write times are just awful but until I had something to show as proof (i.e. SQL Monitors stats/metrics it captures and stores for reporting purposes) I couldn’t make the case for moving off the SANS.
Once I got them to agree to local drives the next battle was the use of VMware. The compromise made was that we would still use VMware but that the VM Session hosting the Windows Server OS + SQL Server would get %100 of the resources on that server and not be shared with any other VM anything on the network. In addition the drives that the data and log files are on would be local attached RAID configurations and not virtualized.
Now I know very little about VMware or virtualization so the above may sound silly or dumb and I wouldn't know. The statement that the data & log drives would not be virtualized may ne non-sesnsical and I wouldn’t know it because I don’t understand how VMware works at that level. The pro-VM admin (who has pushed everything onto VMware so far but our SQL Server ) could be taking advantage of my lack of VM knowledge and feeding me a line of bull so as to make it sound like he's made some sort of compromise with the VM thing when in fact he hasn't and I would not know the difference.
Even though my knowledge in VMware is lacking I have seen enough VMware related problems to know that the Virtualization sales pitch the admins give is not as “You can’t tell the difference between a VM Hosted server and a real non-virtualized server” as they say.
Thanks for replying and if the above helps clarify any of your questions or what I said and you have any additional comments I'd love to hear them.
Thanks
Kindest Regards,
Just say No to Facebook!