VMworld 2012: Virtualizing SQL Server 2012 APP-BCA1516

Speakers: Michael Cory, Jeff Szastak

This session was by far the best this week! 160 slides in 60 minutes, packed to the gills with actionable information on properly virtualizing your SQL server. In short, there’s no reason why you can’t virtualize SQL and have it meet business performance requirements. 80% of SQL performance problems are from storage issues. Below is about 10% of the information from the session. For those of you attending VMworld, run, don’t walk to download their whole slidedeck from socialcast.

If you can guarantee the amount and get it there when you need them, the application should not care if it is virtualized or not.
Why customers are virtualizing biz critical apps? 60% reduction in CapEx, 30% reduction in OpEx, 80% reduction in energy
#1 reason a Windows server crashes are drivers. VMware regression tests all drivers together.
Virtualized DB example: 8TB, 8.8 billion rows, 79K IOPS, 40,000 users, 52 million transactions a day. You can virtualized SQL, no question.
Virtualizing SQL Server 2012

What works in tier-2 does not always work for tier-1 applications
The approach outlined today is conservative.
Read the VMware documentation. Professional Association of SQL Server.
virtualization.sqlpass.org

PLAN, PLAN!!

SLAs, RPOs, RTOs
Baseline current workload
Estimated growths
I/O requirements, Licensing
etc..

Baseline physical database infrastructure
Multiple Tier Approach

Basic (low utilization, test/dev) and premium (production, high visibility)
Different underlying hardware
Different SLAs, RTO, RPOs and HA between tiers

Use ESXtop (KB 1006797)
#1 reason SQL virtualziation fails are storage performance issues. Your database is an extension of your storage.
DBAs need to tell vSphere admins: IOPS/throughput, CPU MHz, Memory Total GB, network bandwidth, Features (clustering), anticipated growth rates.
Do basic throughput testing of the IO subsystem prior to deploying SQL server. SQLIO/IOmeter are the tools to use.
Microsoft fully supports SQL 2005, 2008, 2012 on ESX
How do you install SQL on VMware? Use the same configuration guidelines as physical.
Characterize your workloads

OLTP –
Batch/ETL
DSS

Scale wide for SQL VMs – More VMs, better isolation, better performance, less risk

Don’t do multiple instances on a single VM. Use more VMs, not fewer huge VMs.

Spindle count and RAID configuration still rule
Use more virtual controllers (e.g. upto 4) to allow Windows to queue up more I/O than physical server with just two HBAs.

More I/O inflight to the array with VMware
Understand your physical infrastructure

VMFS vs. RDM = Performance is the same. Don’t use RDMs just for performance. RDMs needed in special cases like clusters.
Use Thick Eager Zeroed disks every time. SUPER IMPORTANT. Database, logs, tempdb. Don’t have to do it for your OS VMDK.
Make sure your array supports VAAI and VASA for best performance
Perform Volume maintenance task right to the SQL service account
TempDB one datafile per database
Always use the PVSCSI adapter – 12% I/O throughput increase, 30% CPU savings
80% of performance problems are due to storage problems
Maintain 1-1 ratio of physical cores to vCPUs to start – increase later
Hyperthreading – 20% uplift in CPU power
Hardware generation matters – Use latest hardware with virtualization features
Memory Settings

SQL Max Memory = VMMem – ThreadStack – OS memory – VM overhead
E.g. 32GB RAM = 28GB set mem max in SQL

Lock pages in memory – SQL service account needs “Lock pages in memory” rights
Size your VM to fit within a NUMA node if possible – Look at total system memory
Avoid shares and limits unless you really understand how they work
Exceeding host memory can cause swapping and tank I/O
Don’t turn off ballooning!
ESXtop stats: KAVG – At zero, DAVG –
Set appropriate reservations – Use configured memory size for reservation
When using reservations – Switch from slot sizes to % for HA configuration
Use large pages switch in SQL server – Easier in SQL 2012
Set packet size to 8192 in SQL 2012 if you use jumbo frames end to end
Use VMXNET3 network driver
Use multi-NIC vMotion in vSphere 5.0
Look at the SQL 2012 licensing FAQ by Microsoft
AlwaysOn Availability Groups – Zero data loss via log shipping and no quorum disks
vMotion will be supported for AlwaysOn availability groups
Use SQL Server Best Practice analyzer