Transcript Virtualizing SQL Server

Tales from the Trenches
About Me
www.dbaexperience.wordpress.com
@shawsql
What We’ll Talk About
•
•
•
•
How Did We Get Here?
Problems
Questions to Ask
Myths
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
Pre-Virtualization
•
•
•
Timeline: < 6 Months
Input: 0
Outsourced P2V
Mood was skeptical and moral was low
What is Virtualization
 Hardware Abstraction
 Pooled Resources
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
What Did We Do?
Performance Monitoring
deviceLatency
vRAM
SAN
pCPU
%RDY vCPU
Network
Host
pRAM
%USED
VM
RDM
SQL
It is all about
looking at the
BIG picture
Performance Monitoring
“When SQL Server is running in a virtual environment, any timebased 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
1. Identify bottleneck using standard SQL Server tools
2. Correlate bottleneck issues using VM specific tools
Performance Monitoring
Memory
•
•
•
•
•
•
CPU
•
Swapped Memory
Ballooned Memory
Reservation
Configured
Consumed (Touched)
Active
Ready Time
Backup and Recovery
WARNING!!!!
Hardware vendors will want to sell you
their SQL Server backup products.
REALITY?
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)
Responsibilities
• SAN
• Servers
• Virtual Infrastructure
• Backup and Recovery
• Storage
• Operations
• Architecture
WHO DO I GO TO FOR HELP?
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
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)
What Would We Do Differently?
Questions: Pre-Virtualization
Physical to Virtual Conversion
 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?
Questions: Pre-Virtualization
Backup and Recovery
 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?
Questions: Pre-Virtualization
Backup and Recovery
You will have options
•
•
•
•
•
Snapshots?
NAS?
Local Drives?
Full Backups or Differential Backups?
3rd Party or Native?
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
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.
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.
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.
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.
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
Other Thoughts
 Server Sprawl
 Vendors
 Resources – More is NOT Better
 Licensing, licensing, licensing
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 VMWare
http://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-87BBC236BFCBA4DF/SQLServer2008_LicensingGuide.pdf
QUESTIONS?