1 / 30

Virtualizing SQL Server

Learn about the challenges, myths, and best practices of virtualizing SQL Server in a large healthcare company's datacenter. Gain insights on performance monitoring, backup and recovery, licensing, and more.

armandos
Download Presentation

Virtualizing SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Virtualizing SQL Server Tales from the Trenches

  2. About Me www.dbaexperience.wordpress.com @shawsql

  3. What We’ll Talk About • How Did We Get Here? • Problems • Questions to Ask • Myths

  4. Pre-Virtualization • Large Healthcare Company • Multiple Hospitals and Clinics • Small Datacenter • New Datacenter • 40,000 + Users • 350+ Physical Servers • 2500+ Windows Servers Includes TIER 1, TIER 2 and TIER 3 Servers

  5. Pre-Virtualization • Timeline: < 6 Months • Input: 0 • Outsourced P2V Mood was skeptical and moral was low

  6. What is Virtualization • Hardware Abstraction • Pooled Resources

  7. Virtualization Effects • Resources Significantly Reduced • No Customer or Vendor Buy-in • Fear, Skepticism, Paranoia • Struggle to Handle DR and HA Environment We scrambled to understand the environment

  8. What Did We Do?

  9. Performance Monitoring deviceLatency pCPU It is all about looking at the BIG picture vRAM %RDY vCPU Network SAN Host pRAM %USED SQL VM RDM

  10. Performance Monitoring “When SQL Server is running in a virtual environment, any time-based measurements reported with SQL Server monitoring tools or Windows Perfmon may be inaccurate if the host machine resources are over-committed” Wanda He http://blogs.vmware.com/apps/2011/03/monitor-sql-server-performance-on-vmware.html • Identify bottleneck using standard SQL Server tools • Correlate bottleneck issues using VM specific tools

  11. Performance Monitoring Memory • Swapped Memory • Ballooned Memory • Reservation • Configured • Consumed (Touched) • Active CPU • Ready Time

  12. Backup and Recovery WARNING!!!! Hardware vendors will want to sell you their SQL Server backup products. REALITY?

  13. Backup and Recovery • Defend the Backup Solution Easiest for Your Team to Manage • Work with the SAN Team to Optimize Storage • Realize Backup Times Can be Longer • Understand Your Backup Retention Plan (and Explain this to your Customer)

  14. Responsibilities • SAN • Servers • Virtual Infrastructure • Backup and Recovery • Storage • Operations • Architecture WHO DO I GO TO FOR HELP?

  15. Responsibilities Don’t Hesitate to Ask for Help in the Following Areas • Storage and IO provisioning • VM Host monitoring • SAN latency issues • Server provisioning and template design • Backup and Recovery implementation • Licensing

  16. Licensing • SQL 2008 DO count hyper-threading. • SQL 2012 DO NOT count hyper-threading • License physical sockets or VMs • License mobility • Software Assurance • Disaster recovery (90 day rule) • Cloud licensing is different (EAP)

  17. What Would We Do Differently?

  18. Questions: Pre-Virtualization • Who is in charge of conversion. Who are the contacts when things go wrong? • Who is responsible for user testing after conversion? • What will be the policy concerning existing physical clusters? • Are there any systems which will NOT be converted? Physical to Virtual Conversion

  19. Questions: Pre-Virtualization • Will our normal backup routines change? Will new hardware vendor products be introduced for backing up SQL Server (SnapManager, Avamar, Syncsort)? • Can the SAN handle our current backup processes? Will there be enough space for backups? • How long will backups be kept? Will backups be stored offsite? • Will the DBA team still be responsible for SQL Server backups? Backup and Recovery

  20. Questions: Pre-Virtualization Backup and Recovery You will have options • Snapshots? • NAS? • Local Drives? • Full Backups or Differential Backups? • 3rd Party or Native?

  21. Questions: Pre-Virtualization Performance Monitoring • Will the DBA team have access to VM monitoring tools? • Will there be a budget to add VM monitoring tools for the DBA team (Ignite VM)? • How is the SAN configured (disk speed, array configuration, tiering?) • What options will be used in VM? Thin provisioning? Ballooning? Reserved space? DON’T LET THE SAN BECOME A BLACK BOX

  22. Questions: Pre-Virtualization Responsibilities • What are the SAN storage constraints? Will I be given more storage if needed? • Who do I contact for additional vCPU or vRAM allocations? • Who do I contact for questions concerning VM performance? • Who is responsible for communicating to the customer questions about the VM architecture? • Think about test environments. Will these change? Don’t get blamed for performance problems caused by infrastructure you have no control over.

  23. Virtualization Myths MYTH 1: You are guaranteed to have worse performance FALSE. VM does cause a slight performance overhead (8-15%) but this is usually offset by faster hardware. MYTH 2: Virtualization will make your job easier FALSE. VM will make some parts of your job easier but complicates other parts. MYTH 3: Virtualization results in storage savings FALSE. Snapshots and templates tend to increase the amount of storage necessary in a VM environment.

  24. Virtualization Myths MYTH 4: All applications will work on VM PARTIALLY. I know of no application that would specifically fail because it is virtualized. Still, large systems like DW may not function to expectations MYTH 5: Performance monitor and other tools are useless in VM FALSE. They are not useless. Just be more skeptical of the results and compare them with VM monitoring tools. MYTH 6: Windows clustering is largely unnecessary in VM TRUE. Windows clusters tend to be redundant in a virtual environment.

  25. Virtualization Myths MYTH 7: Adding additional vCPU will make your vm proportionally faster FALSE. 1-2 vCPU performs at 92% of native, 4 and 8 vCPU performs at 88% to 86% of native. MYTH 8: Virtualization changes your backup strategy PARTIALLY. Some of these choices are based on your SAN. Some are based on your recovery requirements. None of them are based off virtualization.

  26. Best Practices • Use latest version of vSphere (4+). Be aware of licensing changes in vSphere 5 • Group databases with same access patterns onto the same host. • Make sure physical memory on the host is adequate to meet the needs of all virtual machines • If using the balloon driver and locked pages in memory, be sure to set the reservation equal to the amount of memory set in the virtual machine • Use iSCSI 10GB or Fibre Channel. Do not use SATA or 1 GB iSCSI • Understand licensing in a virtual environment

  27. Other Thoughts • Server Sprawl • Vendors • Resources – More is NOT Better • Licensing, licensing, licensing

  28. References • Microsoft SQL Server on VMWare Best Practices Guide (good section on RDM vs. VMFS) http://www.vmware.com/files/pdf/sql_server_best_practices_guide.pdf • DBA Guide to Databases on VMWarehttp://www.vmware.com/files/pdf/solutions/VMware-DBA-Guide-to-Databases-03-11.pdf • Microsoft Licensing Guide for SQL Server http://download.microsoft.com/download/6/F/8/6F84A9FE-1E5C-44CC-87BB-C236BFCBA4DF/SQLServer2008_LicensingGuide.pdf

  29. QUESTIONS?

More Related