Transcript Virtual Server Solution Scenarios

Running SQL Server in a
Virtual Machine
Andrew Dugdell MVP VCP ….
Senior Technical Specialist
Clariti
[email protected]
A Little bit about me…
Andrew Dugdell, [email protected]
Senior Technical Specialist Virtualization by day - Super Villain by night
- Essentially this means I’m working closely with the following technologies daily:
 VMware Server
 VMware ESX
 VMware Workstation
 VMware Converter – old P2V
 Microsoft Virtual Server
 Microsoft VSMT
 Microsoft Virtual PC
 …and nearly every product that bolts on, plugs in, extends out, integrates and
enhances these products in any way :)
 Parallels, Invirtus, Platespin, Surgient, (old) Akimbi, and a few others.
Virtual Machine
BIG User Group
VirtualServer.tv
*
VMware VI3
Agenda
Virtualization of SQL in production
Virtualization Primer: Hosted vs Hypervisor
VM Administrivia
What does not supported really mean?
VM sizing and limits
My findings from the field
disk, consolidation, cpu, shifting hosts, clustering, resources
Baselining
Higher Uptime without clustering
Licensing
General VM health
Q&A
Virtualization of SQL in Dev / Test / QA
*Feel free to ask questions as we go along about anything else that comes to mind.
Virtualization Primer
Two main Vendors: VMware and Microsoft
New Players: XEN, Virtual Iron, Parallels, many more….
Virtualization is an abused term, we are mostly interested
in x86 virtualization
All vendors have both Server Virtualization and
Workstation Virtualization products
Typically a Workstation product is used to author a Virtual
Machine to be hosted on Server product
But this rule of thumb is so very grey and blurry in today’s world.
Two types of server products: Hosted and Hypervisor
We will look at these in the next slide.
Virtual Machines are only be going to be 70% - 95%
efficient as [and compete for resources on] the physical
host
*Tonight we are going to focus on VMware and Microsoft
Virtualization Primer: Hosted
Virtualization Server Software is installed on top an Operating System on top of
Hardware::
 VMware Server
 Microsoft Virtual Server
 Installing software on the Host Operating System is an easy (and very dumb)
thing to do
Virtual Machine
Virtual Machine
Virtual Machine
Server Apps
Server Apps
Server Apps
Operating
System
Operating
System
Operating
System
Virtual
Hardware
Virtual
Hardware
Virtual
Hardware
Virtualization Software: VMware or Microsoft
Host Operating System
Physical Hardware
*Not to scale, and each Vendor has a slightly different architecture
Virtualization Primer: Hypervisor
Thin piece of code separating Virtual Machines from the
bare metal hardware:



VMware ESX – Virtual Infrastructure 3
Microsoft Windows Server Virtualization
Limited if any functionality to install software in the parent partition
Virtual Machine
Mgmt / Parent
Partition
Virtual Machine
Virtual Machine
Server Apps
Server Apps
Server Apps
Operating
System
Operating
System
Operating
System
Virtual
Hardware
Virtual
Hardware
Virtual
Hardware
Hypervisor
Physical Hardware
*Not to scale, and each Vendor has a slightly different architecture
Virtualization Primer: Comparison
The typical differences between Hosted and Hypervisor:
Hosted - Good performance
 Cheap - as in free download
 Run on any commodity grade hardware
 Mess too much with the host and all the VMs suffer
 No oobe dynamic, move the VM if the host is busy
 Typically no prior virtualization policy or planning has taken place
Hypervisor - Excellent performance
 Typically only installed on data centre grade hardware
 No real ability to modify the host and affect VMs
 Dynamic, move the VM on the fly if the host is busy
 An integrator will have done virtualization policy or planning
*regardless of the technology, Virtual Machines are typically isolated from the host
Virtualization Primer: Trivia
Regardless of your Vendor:






The Virtual Machine will always emulate the same hardware, no
matter where it is run
The Virtual Machine will only be up to 95% as efficient as the
physical host – not necessarily a bad thing
Moving a Virtual Machine to a host with faster CPU and/or faster disk
subsystem will pass that benefit into the Virtual Machine
Virtual Machines are easily interchangeable between hosted and
hypervisor installations
Virtual Machines are more or less portable between VMware and
Microsoft virtualization solutions – with a little bit of work.
Virtual Machines are just a big file on a filesystem somewhere and
can be easily copied and duplicated
*
Virtualization Not Supported?
This is my only rant slide *sigh* 
Unsupported does not mean it does not run
Just because a product isn't supported, doesn't necessarily mean people will
stop using it and getting value from it :)
In my world, un-supported usually means KB 897615 - your mileage may
vary:
Windows Server System software not explicitly supported within a VM guest: KB
897614
Virtual Server supported: KB 897613
Exchange 2003 Explicitly Supported as a VM Guest: KB 320220
Operating Systems that support Virtual Server as a guest and as a host: KB
867572
Support policy for Microsoft software running in non-Microsoft hardware
virtualization software : KB 897615
Third-party guest operating systems (Linux) supported with Virtual Server 2005 R2:
KB 917437
Virtualization support and licensing is currently undergoing huge changes
across the field. Watch this space
*18 months ago there was no explicit support for virtualized Exchange; even on Microsoft Virtual Server.
On to why we are here
Running SQL in a VM
*
Virtual Machine Sizing
Virtual Machines run in “emulated” hardware with limitations of:
Virtual Server
VMware Server
ESX 3.0
WSV
Microsoft
VMware
VMware
Microsoft





1x CPU
3.6 Gb Ram
32 bit
~128 VMs
No LUN
support





2x CPU
2/4Gb Ram
32/64 bit
~128 VMs
No LUN
support





4x CPU
16Gb Ram
32/64 bit
~128 VMs
Raw Disk
Mapping ~
256 LUNS






8x CPU
32Gb Ram
32/64 Bit
~  VMs
Raw Disk
Mapping ~
256 LUNS
Hot add
RAM/
CPU/ NIC
*guideline only - depends on the configuration and resources available on the host
Virtual Machine Misconceptions
Here are some common myths, busted.
Anti-virus on the host, does not protect the VM
A firewall on the host, does not protect the VM
Patching on the host, does not protect the VM
You should defragment inside your VM
You can ghost / image within a VM
You can even (with a little effort) restore a
physical host inside a VM
You should always install the VM Additions / VM
Tools as they will give noticeable performance
improvements
*Come on don’t be shy – who hasn’t installed the additions?
SQL in a VM – my findings
Disk
Disk IO tends to be the no 1 killer
Use multiple virtual disks, just like physical.
Keeps queue length down and allows moving to
separate physical spindles on the host
Use LSI on VMware
Use SCSI on Virtual Server
Raw disk mapping is not faster, just better SAN
replication and accessibility – eg: a physical 2
virtual cluster
*
SQL in a VM – my findings
Consolidation
Existing SQL 6.5 and 7.0 boxes P2V well
New SQL licensing is a good incentive to
consolidate
Consolidation does not always mean good
performance; remember baselining
Perfect for preserving OS:SQL combinations that
don’t work together or aren’t supported together
*
SQL in a VM – my findings
CPU
Under VMware don’t add extra CPUs just
because you can – requires additional overhead
on the host
Some good performance gains have been
achieved by using 2 smaller VMs instead of 1
larger VM
*
SQL in a VM – my findings
Shifting Hosts
Typically SQL servers VMotion ok between hosts
~ 500 ms downtime
SQL servers Host Cluster ~ 10 seconds
Homegrown SQL client apps sometimes
“complain”
Know what “complains” before migrating under
peak load
Your mileage will vary
*
SQL in a VM – my findings
Clustering
High Availability, Vmotion, Host clustering, don’t
necessarily exclude the need for clustering – just reduce
it
You can still cluster VMs, on clustered hosts – don’t do it
“just because you can”
KB 910048; The operating system stops responding
when you run Windows Server 2003 SP1 in a VMware
environment
The hotfix is now in Windows 2003 SP2; KB 914962
Windows SP2 includes various performance ‘fixes’ for
both Virtualization AND SQL
*
SQL in a VM – my findings
Insufficient resources on the host
VMs by design can’t freely communicate with the
host
It’s not an easy for a VM to tell if the host is
starved for resources
Baselining and stress test is important prior to
commissioning the VM
Use tools such as MOM or Virtual Centre
*Ideally have some mechanism within your VM to see if the resources assigned to you are available to you
SQL in a VM – Baseline
If I could go back in time I would
Stress test the VM and check that peak
performance is acceptable
Ensure the VM is monitored when expected
performance is breeched
Use tools such as IOmetre, SQLIOSIM
Has anyone run the database sizing tools
Know the primary SQL workload
Baseline when the host is running just your VM
Baseline when the host is running all VMs
Stress the host and test I/O latency within your SQL VM
*
So why Virtual Machines
Why would anyone want
to run production SQL
in a Virtual Machine?
*
Why – Higher uptime?
Moving VMs between hosts
Reboots are less than 2 minutes, most Raid
packs can’t power up in this time
Host Clustering / High Availability
Higher uptime without clustering
*
Why – Mitigate Risk?
Why do Infrastructure people like VMs?
It is easier to make your Testing / DR
environment indicative of production
Backups, restores, snapshots, etc
Replacing the host at the end of it’s lease is a
no-brainer
Adding RAM, CPU, Storage to a VM is a nobrainer
Offline maintenance is easy
It’s easier to justify a new Virtual Server,
compared to a physical server
*
VM Licensing
I’m not a licensing guy
New Windows R2 Licensing
New Window Data Center Licensing
New SQL Enterprise Licensing
Check the SQLVirtualization.doc whitepaper on
Microsoft Downloads [n65]
Check the upcoming webcast (I’ll be there)
Virtualization and SQL Server:
Technologies, Best Practices and Pricing/Licensing
4/2/2007 9:00 AM (PST )
*I’m hoping for lots of changes in this space this year
VM administrivia
VMs need maintainance as well
Defrag OS + Filesysyem + Pagefile in your VM
Reduce low level calls that utilise low level
instructions such as MMU, TLB, Etc…
Patch!
Hosted Products need maintainance as well
Defrag OS + Filesysyem + Pagefile on your host
Do not scan or index directories with Virtual
machines
There is a list of items to check on my blog
*
Virtual Machine Summary
Virtual Machines aren’t necessarily a bad thing
Virtual Machines have limitations, just like
physical machines
Virtual Machines that haven’t been baselined or
stress tested is a bad thing
Virtual Machines that aren’t monitored is also a
bad thing – naughty too!
Virtual Machines are easier to manage for
operations/infrastructure guys.
*
Virtualization of SQL in production
Virtualization Primer: Hosted vs Hypervisor
VM Administrivia
What does not supported really mean?
VM sizing and limits
My findings from the field
disk, consolidation, cpu, shifting hosts, clustering, resources
Baselining
Higher Uptime without clustering
Licensing
General VM health
Q&A
Virtualization of SQL in Dev / Test / QA
*