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
  • 10ms>

Print Friendly, PDF & Email

Related Posts

Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments