This is a live update from VMworld! These are just my notes during the session, please forgive the grammatical errors, I am sure I missed parts of the lecture as I am taking notes…
Be sure to check out the actual session recording at http://www.VMworld.com – search for the session using the session identifier in my blog post title
Speaker: Scott Salyer
Overview of SQL on VMW
Many customers want to virtuallize more for lower costs, simpler mgt, better availability and agility. However,many application owners need convincing. Pretty much everyone agrees that after virtualizing all the low hanging fruit, business critical applications are next. According to VMW surveys, Sharepoint, SQL, Exchange, SAP and
Oracle are all being virtualized to increasing sped on VMW.
So Why Virtualized?
– Consolidation of instances on fewer systems, and consolidation of licensing
– Operqtional Advantages
– Higher availability through the use of vMotion, Ha, SRM
Tier 1 workloads- Architecting for Performance
Resource Allocation
vSphere 5 now capable of MONSTER virtual machines with 32 vCPU, 1TB RAM, and up to 1000000iops. How does virtualized SQL scale compared to physical? At 1-2 vCPU, virtual was at 92% of native, and with 4vCPU, virtual ran at 88%of virtual. there was virtually no difference between them regarding disk performance.
when testing scale out, performance scaled linearly when going from 1-8vCPU, with no drop in performance until the ESX host itself was at 100%.
what kinds of workloads can be virtualized?
– OLTP- large amount of small queries, with sustained usage during business hours
– Batch- write intensive, run during off hours, sustained usage during the batch
– DSS- small amount of large queries
NOTE- due to the different utilization models, different kinds of databases might coexist very nicely on the same host/cluster
How to design for SQL, then?
1- Monitor existing workloads, if not then estimate activity
2- allocate CPU and RAM
3- design storage for capacity and performance (most crucial step)
4- optimize guest os and SQL
Guidelines-
– allocate the minimum vCPU and ram requirement and adjust as needed
– Try to avoid CPU overcommitment – observe CPU counters
– right size your memory, use memory compression with vSphere 5
– understand your disk I/O requirements, use redundant paths
– NUMA now extended in vSphere 5 to the guest (SQL can take advantage of this)
– SQL will automatically optimize its processing for NUMA if it detects it
– use shared storage, create VMFS from vCenter to ensure alignment
– format VMDK files as eager-zero-thick for maximum performance for database and log files
– use multiple vscsi adapter to distribute the target devices
– ensure your Windows partitions are aligned (up to a 50% hit if not)
– RDMs are required for clustering, or SAN-specific tools
– If using VMFS, you might need to maintain a 1:1 ratio of vm to datastore (this should be a joint effort)
– the combined ions of all vms on the LUN must be less than the rating of the LUN
– use Microsoft guidelines for layout of disks
SQL Server Guest Tuning
– Use large pages in the t
– enable Lock pages in Memory for SBC account
– follow MS Best Practices
– preallocate data files to prevent autogrow
– place tempdb ,data, and logs on separate luns
– perform your routine maintenance
SEE THE ONLINE REPLAY FROM VMWORLD FOR A DISCUSSION OF MONITORING
Database and licensing Consolidation
The vast majority of SQL databases are over provisioned and underutilized… VMW found the average SQL server uses 2 vCPU and only 6%utilization, average ions is only 20, with 90% under 30% utilization and under 100 iops.
Be aware of what business impact ther is to a migration… Is there a hardware lease coming up? a software contract renewal?
Conventional db consolidation is difficult, regardless whether you pursue multiple instances or single instance. Remediation and support of these is protracted and never complete.
By virtualizing, you retain isolation of the workload, prevent change to the application, and retain resource guarantees. You can still scale up within a single vm, though this lends itself to fewer larger virtual machines. scaling out generally provides better performance and isolation, thou many customers will find a use case for both. By consolidating SQL through virtualization, you increase CPU utilization while decreasing licensing costs, assuming your SQL systems are over provisioned and underutilized. Refer to Microsoft for the final word on licensing, but VMW has found that Server-based licensing is generally cheaper in the long run than CPU. Be aware that licensing doesn’t necessarily move with the vm if you are using Standard licensing. SQL can only move once every 90 days in a non-failure scenario. SEE THE ONLINE REPLAY FROM VMWORLD FOR A DISCUSSION OF LICENSING
Availability and Recovery Options
You must understand your availability requirements… Leverage vMotion, DRS, and HA. These will provide protection against hardware-level failures or BSOD, but no protection against application outages. SQL mirroring will provide service availability for patches and upgrades, but you must create 2 copies of the data, RTO of 2-3seconds. Use HA and mirroring for a robust recovery scenario.
Failover clustering leverages shared storage offers automatic failover for any application or user, but imposes severe limitations on virtualized models. Non shared disk cluster models offer better support for virtualization. Failover clusters with shared disk supports RDM only, 2-node cluster, Active/Passive.
There is a whitepaper forthcoming from VMW on patching methodology.
Tom Twyman-
Fr0gger03
My Occasional IT Blog... 