sql saturday dc vm ware

Virtualization for DBAs

Joey D’Antoni

November 5, 2011

About Me

Senior SQL Server DBA at Comcast Blog: joedantoni.wordpress.com Twitter @jdanton Email jdanton1@yahoo.com

11/4/2011 |

Footer Goes Here2 |

Virtualization

Major Players Terms Costs and Benefits Technology Optimizing SQL for a Virtual Environment Summary

Major Virtualization Players

It seemed like a good idea at the time…

Server Room Sprawl

Server sprawl SQL sprawl Power and Cooling Issues in DCs Broader availability of SAN storage

Terminology

Guest—The virtual server running underneath the physical host and hypervisor (instance of an Operating System)

Host—The physical server that your virtual machines run on

Hypervisor—The underlying software that performs the load balancing and sharing of resources between guest operating systems

Terminology (cont’d)

Thin Provisioning—Allowance in virtual environments to overallocate physical resources (more to come later)

Deduplication—Process of compressing memory/disk space by saving only one copy of common bits

vMotion/LiveMigration—Process which moves guest OS’s from host with high resource utilization to lower. Also an HA function with the hypervisor

Terminology (cont’d)

Snapshot—A full point in time backup of your guest OS (very handy for upgrades/patches/code releases)

Cloning—The process of building a gold guest image in order to rapid deployment

Costs

VMWare isn’t cheap Licensing about $25k per server for a Enterprise

plus on a decent sized server Licensing changed from CPU—to CPU with

memory grants, allowed 96 GB per CPU license

Hyper-V Included with your Windows Server Licenses

(amount of VMs vary based on edition) SCOM, while not required is recommended

Benefits of Virtualization

Lower cooling and power Higher utilization of hardware Can be used for HA configurations Rapid Deployment of new environments Use Gold Standard servers and rollout Snapshots

How this works…

Host

Hypervisor

Guest Guest Guest

One

Phy

sica

l Ser

ver

What does the hypervisor do?

Manages resources between guest O/S Memory management Backups Failover and DR

VMWare Architecture

Snapshots

HA and DR

Typical Hardware

Virtualization hosts are the typical servers you might run SQL Server on.

2 x 4-6 core processors (Dual socket servers represent 80% of install base)

A Lot of RAM

Thin Provisioning

Allows over allocation of resources

Increases storage provisioning

Management console allows for easy management of this along with SAN

NOT GOOD FOR PRODUCTION DB SERVERS!!!

Shared Environment vs Dedicated Environment

Multi-Tenant Environments

This can make monitoring and baselining your server more challenging

You will want to have open communications with your VM administrators

Ask for view access into VCenter—it will show you what else is going on in the environment

CPUs

Can be over allocated Use servers with the newest chips—they are

optimized for Virtual Workloads Maintain 1:1 ratio of physical cores to vCPU

for production boxes For production workloads you may want to

dedicate CPUs to the machine

Memory Management

Memory can be over allocated (but don’t do it for production!!!)

Hypervisor handles it by de-duplicating memory.

Host Page Files

Balloon Driver

Balloon Driver

When hosts comes under memory pressure, VMWare reclaims memory from guests

Storage

I/O Concerns

Two choices of file types—VMFS (VMWare File System) and RDM (Raw Device Mapping)

Performance between two is similar RDM is required for clustering VMFS generally more flexible Use Shared Storage (SAN) to get HA and DR

functionality

I/O Concerns

Partition alignments still matters < Windows 2008

Work with storage team to monitor I/O—Hypervisors can have strange I/O patterns

Virtual Server

Virtualizing SQL Server

Use Trace Flag –T834—large pages enabled Set min and max memory—this will lock

SQL’s memory to prevent possible balloon driver impact

Follow the same storage best practices you would for a physical box (Separate TempDB, Data, Logs)

Test out I/O performance before beginning

Monitoring SQL Server

From the server perspective everything stays the same

Everything may not match at times Ask for access to the vSphere client!

It’s the only way to have an overview into the broader system

Performance Issues

Troubleshoot as you normally would, then check VMWare

Similarly with a SAN—try to identify what you apps are sharing your resources

Can adjust load on the fly by using vMotion (or Live Migration)

Summary

Virtualization is the future, and the future is now!

Virtual servers work from a shared resource pool and that can impact your workloads

Identify changes you need to make to your SQL Servers for Virtual Environments

Get access to your virtualization management layer

Questions?

Contact Info

Twitter: @jdanton Email: jdanton1@yahoo.com Blog (slides): joedantoni.wordpress.com