Managing SQL Server in a Virtual World Notes (View Complete Slide Show)
Hosted by MVP Kevin Kline, Quest Software
Guest presenter Jason Hall, SQL Server consultant to Quest software
How to optimize your SQL Server licensing with VMware, since most SQL VMs are on VMware ESX platform: http://communities.vmware.com/message/1075017
Why VM? Minimize idle resource time, drive down power costs
Reduce rack space and physical resource waste - SPRAWL.
Enhanced DR/HR options.
MSFT has embraced VM technology - vendor-neutral support, despite HyperV push.
VM host/guest clustering. Dedicated admin group
Grant access to the DBAs to allow them to manage/view SQL VMs
Vmotion makes it all online. HyperV live migration and VMware vMotion
- zero downtime to failover. When you move a guest from one place to another there is the possibility of resource contention. Very slick stuff.
VMotion/Live Migration failovers can happen without notice, behind the scenes - it is entirely automated - just like automatic failover in Mirroring (some application may not want it).
DB backups are never going to become unnecessary.
Do not rely exclusively on VM image backups. More backups are better.
DB backups are still critical for moving between environments due to all the scripts that you will need to run.
Backup caveats:
Shared everything - watch for bottlenecks. Stagger your backups - avoid kicking off everything at the same time, especially when using compression.
Do not need to do full backups all the time, take advantage of differential and Transaction log data.
Distributed backups.
CPU over allocation. Very easy to over-allocate an ESX server.
vmotion event moves VM to an already heavily used host.
4VMs that are typically each used at 20% each go to 30%
Check CPU ready % - VM is ready to do work but host does not have the avail. Resources - strive for 0% over 10% sustained can be a critical problem.
When the CPU is allocated VMware needs to determine how to divvy up the CPU resources
CPU monitoring. GET slides of the entire presentation
Memory monitoring. Page life expectancy is the best metric to understanding memory pressure.
if we require more memory that we have available, VMware must repossess memory.
Memory ballooning Swapping
Make nice with your VM Admin 🙂 NEVER!! I refuse!! Damn those VM admins 🙂 !!!
From QA: Answer: I'll answer that now - SQL Server sees the VM as if it were the real, physical machine. So if the VM is provisioned with more than one CPU, then parallelism is possible. SQL Server then carries on as normal. If the VM has only 1 cpu, then not.
Question: It is not only about being nice to the Admin - it is about segregation of duties, often one has to respect that for compliance (my comment)
Answer: Great point!
Question: would image backups be a good case for SQL Server Upgrade in place as in SQL 2000 to SQL 2008
Answer: Assuming you've stopped processing on the SQL Server you're upgrading, then it should be fine. Otoh, I thought that SQL2k required an upgrade to SQL2005 before going to SQL2008. (But I could very well have that wrong. It's been years since I looked at SQL2k).
Question: What about offsite HA. Current region has a failure(fire in server room)
Answer: I'll ask, but I think the VM platforms support geographically dispersed backups.
Question: You can upgrade SQL 2000 SP4 to SQL 2008
Answer: Cool - then do it today! 🙂
Question: SQL 2012 and always on allows a geographically disperse backup chain although i wouldn't recommend it.
Answer: Hmmm - I thought AlwaysOn looked pretty solid. Why the reservations?
Question: Will you cover MS SQL Server licensing under a VM architecture?
Answer: Gotcha covered: http://communities.vmware.com/message/1075017
Question: Introducing Microsoft SQL Server Code Name "Denali" Preview Content states that "the transaction log backups do not have to be performed on the same replica". e.g in an AlwaysOn Config with one primary and 2 secondary - one log backup could be taken at 10:00 in Washington one at 10:30 in London and one in Sydney at 11:00 and the log chain is preserved accross the primary and both secondaries. I would recommend having my backups in the same location.
Answer: It depends on the business use case. In addition, it doesn't say you cannot do the backups in the same place - only that it also supports backups on more than one replica.
Question: Are there any advantages to creating a guest VM with multiple virtual cpu's as opposed to a single cpu?
Answer: Sure. SQL Server sees the VM configuration as if it were the real, physical machine. So if you want SQL Server to do any parallelizing of transactions, you'll need more than one CPU.
Question: What are the perf counters for monitoring memory balooning?
Answer: Not sure, but I think Jason hits those in a minute.
Question: If anyone wants to understand Guest Versus Host clustering on VM, please see the downloads and demos from this presentation I gave at Tech Days here in Montreal last year: http://www.sqlservercentral.com/blogs/hugo/archive/2010/11/22/guest-vs-host-clustering-virtual-machines-speaking-tuesday-23rd-november-at-montreal-tech-days.aspx
Answer: Awesome - thanks for sharing, Hugo!
Question: We use "Lock Pages in Memory" on all our SQL servers, say again why this is bad? And we got this from Microsoft
Answer: The setting prevents SQL Server from swapping memory out for other uses. So what happens when you start a 4gb file copy? Memory shortages. This setting is only useful on HIGHLY tuned and well researched systems.
Question: Perf counters for VMware?
Answer: Check this white paper:
http://www.vmware.com/files/pdf/perf_vsphere_sql_scalability.pdf
VM ware over head in Spotlight if the best metric to understand if there is an overloaded ESX environment.
If there was an over-allocated VM metric, it would be there.
Virtualised memory - the true memory in blue - watch for Mem over allocation.
