DBA Guide to Databases on VMware

DBA Guide to Databases on VMware
© 2011 VMware, Inc. All rights reserved.
Page 2 of 32
© 2011 VMware, Inc. All rights reserved. This product is protected by U.S. and international copyright and
intellectual property laws. This product is covered by one or more patents listed at

5. Developing and Testing Databases on VMware 17
5.1 Testing and Troubleshooting with Production Clones 17
6. Migrating Existing Databases to VMware 19
6.1 Physical–to-Virtual Conversion 19
6.2 New Database Installation 19
7. Securing the Databases 20
8. Running Databases on VMware vSphere 22
8.1 Reduce Planned Downtime 22
8.2 Reduce Unplanned Downtime 23
8.3 Manage Patch Upgrades 23
8.4 Manage Backup and Recovery 24
8.5 Manage Legacy Databases 25
9. Monitor and Troubleshoot Databases Performance 26
9.1 Performance Monitoring Tools 26
9.2 Key Performance Metrics on vSphere 28
10. Frequently Asked Questions 29
10.1 Common Questions from SQL Server DBAs 29
10.2 Common Questions from Oracle DBAs 30
11. Conclusion 32
DBA Guide to Databases on VMware
© 2011 VMware, Inc. All rights reserved.
Page 4 of 32 DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 5 of 32
1. Introduction
Virtualization is rapidly eliminating the old ―one server one application‖ model and has freed

locations.
Today, virtual machines on VMware vSphere 5 can scale to 32 virtual CPUs, 1TB of memory per
virtual machine, and over 1,000,000 disk IOPS, while keeping overhead limited between 2 – 10
percent. That’s a 20x performance increase over ESX 2. This clearly demonstrates that virtual
machines running on VMware vSphere can scale to meet mainframe-size workload demands.
Additionally, vSphere maximizes the performance achieved from physical hosts by enabling
multiple databases to efficiently share the large capacity of multicore servers.
Today’s new 64-bit servers come with growing numbers of CPU cores, higher memory limits, and
increased network bandwidth. The majority of the database applications can run comfortably with
a fraction of the server capacity. The traditional deployment model of one application per server is
not keeping pace with the latest developments in hardware and virtualization. Virtualizing
databases on vSphere simultaneously consolidates the databases and optimizes compute
resources, while maintaining application flexibility by isolating each database in its own virtual
machine. You can migrate databases from physical to virtual environments in their current state
without expensive and error-prone application migrations. DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 6 of 32
The value of virtualization goes far beyond basic consolidation. Virtualizing database applications
on vSphere can improve application Quality of Services (QoS), and accelerate application
lifecycles while significantly reducing application costs.
 Improve application Quality of Service – Databases are very difficult to size on physical
servers. With VMware, databases can scale dynamically to meet changing throughput
requirements. You can leverage vSphere High Availability (HA), vSphere Fault Tolerance
(FT), VMware vSphere vMotion
®
, Dynamic Resource Scheduling (DRS) and VMware vCenter

the database software is typically up to the DBA. The DBA role requires knowledge of the
hardware prerequisites for an efficient database server, and communicating those
requirements to the system administrator. The DBA installs the database software and
selects from various options in the product to configure it for the purpose for which it is being
deployed. As new releases and patches are developed, it’s the DBA’s job to decide which are
appropriate and to install them. If the server is a replacement for an existing one, it’s the
DBA’s job to get the data from the old server to the new one. DBAs are tasked to provision
DB servers on demand for development, testing, QA, and reporting. DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 8 of 32
 Database security – Databases centralize the storage of data and are attractive targets for
hackers. DBAs must understand the particular security model that the database product uses
and how to use it effectively to control access to the data. The three basic security tasks are
authentication (setting up user accounts to control logins to the database), authorization
(setting permissions on various parts of the database), and auditing (tracking who did what
with the database). The auditing task is particularly important as regulatory laws such as
Sarbanes-Oxley and HIPAA have reporting requirements that must be met.
 Backup and recovery, high availability – DBAs are responsible for developing, implementing,
and periodically testing a backup and recovery plan for the databases they manage. Even in
large shops where a separate system administrator performs server backups, the DBA has
final responsibility for making sure that the backups are being done as scheduled and that
they include all of the files needed to make database recovery possible after a failure. When
failures do occur, the DBA needs to know how to use the backups to return the database to
operational status as quickly as possible, without losing any transactions that were
committed. There are several ways a database can fail, and the DBA must have a strategy to
recover from each type of failure. From a business standpoint, there is a cost to doing

significant focus on maximizing the performance of virtual machines and vSphere 5.0 offers
tremendous progress in IO, CPU, and memory scalability over early product generations.
Today, virtual machines on vSphere 5.0 can scale to 32 virtual CPUs, 1TB of memory, and over
1,000,000 disk IOPS, while keeping overhead limited between 2 to 10 percent for the majority of
applications.
Figure 2. Application Performance on ESX In February 2009, VMware vSphere set a new benchmark in virtualized database performance.
VMware vSphere was benchmarked with one of the most demanding workloads for virtualization:
a resource-intensive OLTP database based on a fair-use implementation of TPC-C. This
application is significantly more resource-intensive than average production databases, and puts
a heavy load on the hypervisor. Even for this difficult workload, a single virtual machine in
VMware vSphere running Oracle 11g and Linux achieved 85 percent of native performance with
near-linear scalability from one virtual CPU to eight virtual CPUs. The virtual machine supported
8,900 transactions per second and drove about 60,000 disk IOPS—a massive amount of
throughput that only a small fraction of databases actually require. DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 10 of 32
There are some exceptionally large databases out there, but it is rare that a database can exceed
the performance capabilities provided by VMware vSphere. The reality is that almost all
databases can run comfortably on VMware vSphere, with plenty of processing headroom to
spare. Based on VMware Capacity Planner data compiled from tens of thousands of production
servers in customer environments, the average production Oracle database requires only a
fraction of the capacity that a virtual machine can deliver.
Figure 3. Average Oracle Database Fits Easily in a Virtual Machine

that may only happen once a year, or to handle any unexpected resource needs.
With VMware vSphere, resources are managed in pools. CPU, memory, storage, or network can
be increased or decreased dynamically, or manually with just a few mouse clicks.
4.1.1 VMware Hosts, Clusters, and Resource Pools
VMware hosts, clusters, and resource pools provide flexible and dynamic ways to organize the
aggregated computing and memory resources in the virtual environment and link them back to
the underlying physical resources.
A host represents the aggregate computing and memory resources of a physical x86 server. A
cluster acts and can be managed as a single entity. It represents the aggregate computing and
memory resources of a group of physical x86 servers sharing the same network and storage
arrays. For example, if the group contains eight servers with four dual-core CPUs each running at
4GHz and 32GB of memory. The cluster then has an aggregate 256GHz of computing power and
256GB of memory available for running virtual machines.
Resource pools are partitions of computing and memory resources from a single host or a cluster.
Resource pools can be hierarchical and nested. You can partition any resource pool into smaller
resource pools to further divide and assign resources to different groups or for different purposes.
4.1.2 VMware Hot Add
The VMware Hot Add feature enables hot adding CPU and/or memory to a running virtual
machine. As applications grow over time and require more CPU or memory resources, a DBA
can scale up virtual machines dynamically and on the fly. Both Oracle and SQL Server can detect
the new capacity, and make use of the additional resource in subsequent operations. DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 12 of 32
4.1.3 VMware vSphere vMotion
VMware vSphere vMotion enables live migration of virtual machines from one physical server to
another without service interruption. vMotion uses the VMware cluster file system to control

disaster recovery; for example, SQL Server failover clustering, database mirroring, log shipping,
Oracle RAC, and Data Guard. Though all of these are good choices for database recovery, the
application-centric nature of these technologies are typically costly, complex to set up, with high
operational overheads, and are single database-, or single instance-based. Implementing high
availability and disaster recovery for every database in a physical environment is often not
practical.
The VMware vSphere platform ships with built in high availability. By decoupling the operating
system and application from the underlying physical server hardware, a virtual machine can
easily move from one physical server to another.
4.2.1 vSphere High Availability
vSphere High Availability (HA) monitors virtual machines to detect operating system and
hardware failures, restarts virtual machines on other physical servers without manual intervention
when hardware failure is detected, and protects applications from operating system failures by
automatically restarting virtual machines when an operating system failure is detected. DBAs no
longer have to worry about the database going down because of failures associated with the
operating system, HBA, network card, or other hardware components.
Because HA can be configured with a single click from within the vSphere Client interface to
provide failover protection for all virtual machines without requiring the complex setup and
configuration of clustering, DBAs can design uniform, cost effective failover protections against
operating system and server failures for all databases regardless of the server hardware or
operating system used by the database virtual machine. vSphere HA can provide a simple and
reliable first line of defense for all databases without the cost and the hassle of traditional
database high availability options. vSphere HA can also be used in combination with native
database high availability options such as SQL Server database mirroring or Oracle Data Guard
to provide complete protection from both hardware and application software failure.
4.3 Design Simple and Reliable Database Disaster Recovery
A VMware virtual machine is essentially a software container that bundles or encapsulates a
complete set of virtual hardware resources, as well as an operating system and all its
applications, inside a software package. Encapsulation makes virtual machines portable and easy
to manage. An entire database virtual machine can be contained in a small set of files, and can

significant administration challenge for DBAs.
Databases also tend to be the most over-provisioned applications in the datacenter, and can be
very expensive due to high license costs and top-tier infrastructure requirements.
As computing power for servers continues to increase, many organizations are moving toward
database consolidation. The question for a DBA is often how to consolidate. Conventional
database consolidation is typically performed by either running multiple instances of the database
software on a shared OS image (multi-instancing), or by running multiple databases within an
instance (shared instancing). These traditional approaches can be successful, but also pose
significant challenges:

DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 15 of 32
 There is generally a lack of OS configuration, security, fault, and resource isolation between
database instances. A single OS or database failure could result in dozens of databases and
applications being down simultaneously.
 Load balancing between physical hosts is a complex undertaking that requires re-
provisioning databases. A large workload spike on one physical host could result in
unacceptable performance for many databases at the same time.
 It can be difficult to guarantee resources to any individual database. One misbehaving
database could take the resources of other, more critical databases.
VMware virtualization technology offers a much simpler and more efficient alternative for
database consolidation. Consolidating databases with VMware vSphere delivers several unique
benefits over conventional approaches:
 Fast consolidation with P2V – With VMware vSphere, consolidating existing databases is
simple. Databases can be migrated with a physical-to-virtual (P2V) migration, or re-
provisioned in a virtual machine with their existing OS and database configurations. This
eliminates the need to re-test and update databases to run on standardized OS and database
configurations.


© 2011 VMware, Inc. All rights reserved.
Page 16 of 32
Figure 6. Customer Scenario on Consolidating SQL Server Licenses
DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 17 of 32
5. Developing and Testing Databases on VMware
DBAs often need to create test environments for developers and QAs. Deploying a new database
server can take many hours to acquire the hardware, install the operating system and patches,
and install the database software and updates. In the physical environment, the process may take
days or weeks. Often, the time needed to create the test environments exceeds the amount of
time the environment is actually used.
VMware templates allow you to clone, convert, and deploy virtual machines based on a base
image. You can set up and configure a single database virtual machine that incorporates best
practices, then use the VMware template feature to convert the virtual machine into a template.
The template can then serve as the ―golden‖ image and be used for subsequent database virtual
machine deployments. This not only saves countless hours during new database system
deployments, but also results in all deployed systems having identical configurations that follow
best practices.
Alternatively, via a self-service portal, VMware vCloud
®
Director™ allows developers to create,
share, and configure their own development and test environments without having to request
these environments from the IT department. Because these test environments live in a virtual
world they can be deployed, used, and retired without risk to the production environment.

environment, and migrate the data from the legacy physical server.
6.1 Physical–to-Virtual Conversion
VMware vCenter Converter™ can be used to convert existing physical database servers to virtual
machines through a process commonly referred to as physical-to-virtual (P2V) conversion.
VMware vCenter Converter simplifies the P2V conversion through intuitive wizard-driven
automation. With vCenter Converter, DBAs can convert multiple local and remote physical
database servers simultaneously from a centralized management console.
To maintain consistency of the database, it is recommended that you stop the database services
(but leave the OS running) during hot cloning of the physical database server.
P2V conversion through vCenter Converter accomplishes a one-to-one mapping between the
physical server and the virtual server. That is, if there are multiple instances and/or multiple
databases running on the physical server, the resulting virtual machine will have the same
number of instances and/or databases. This may not be the best approach for achieving optimal
resource utilization or isolation with consolidation.
6.2 New Database Installation
Existing physical databases can be migrated to a virtual machine by performing a new installation
of the database software on the virtual machine, and then migrating the data to the virtual
machine from the source physical server.
This approach allows you to upgrade the database version along with migration. For example, if
you have a SQL Server 2005 server that you are virtualizing, you can choose to upgrade the SQL
Server 2005 instance by installing a new SQL Server 2008 R2 instance on the virtual machine,
and then restoring a backup copy of the SQL Server 2005 database onto the SQL Server 2008
R2 instance.
If Raw Disk Mapping (RDM) is used in the database virtual machine, database files can be
detached from the physical database, and attached to the database on the virtual machine. The
entire backup/copy/restore process is eliminated, so cutting over from the physical server to the
virtual server could be accomplished in seconds. This is especially useful for migrating databases
with large on disk footprints and/or for mission critical databases that demand high uptime.
For mission critical databases that demand high uptime this migration approach also enables the
use of native database replication features such as SQL Server database mirroring, and log

Figure 8. vShield App Securing Business Critical Applications
DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 21 of 32
VMware vShield App addresses common challenges to application security within virtualized
environments as you:
 Eliminate blind spots – Define and enforce granular policies for all traffic between
applications, increasing visibility into traffic while helping to eliminate detours to physical
firewalls.
 Maintain change-aware protection – Prevent network topology changes from impacting
application security with continuous firewall protection for virtual machines as they migrate
from host to host.
 Accelerate IT compliance – Get increased visibility and control over virtual machine network
security with the logging and auditing controls you need to demonstrate compliance with
internal policies and external regulatory requirements.
The hypervisor-level firewall in VMware vShield enforces proper segmentation and trust zones for
all database deployments. vShield App also works in combination with native database
encryption features such as transparent data encryption and backup encryption to protect data
from unauthorized access. DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 22 of 32
8. Running Databases on VMware vSphere

mirroring, and log shipping.
Support for servers with multiple network and storage interfaces is built into VMware ESX/ESXi,
significantly cutting the cost of fault-tolerance by sharing redundant hardware between multiple
virtual machines. vSphere HA provides protection from server hardware failure. vSphere
Distributed Resource Scheduler (DRS) can reduce unplanned downtime by automating the use of
vMotion to migrate running applications away from servers that cross utilization thresholds.
8.3 Manage Patch Upgrades
Similar to a running a database in the physical environment, it is a best practice to patch
database virtual machines regularly to keep up-to-date with bug fixes, security updates, and so
on. Most database environments have change control procedures that require patches to go
through some form of testing before production deployment.
8.3.1 VMware Snapshots and Clones
VMware snapshots and clones can be used to streamline the testing of configuration changes,
patches, or upgrades to your database servers. When you need to upgrade your production
database systems, cloning allows you to quickly recreate an exact copy of the production
environment in the lab, increasing the reliability of testing. After the patches or upgrades have
been applied and validated, the same upgrade procedure can be reproduced in the production
environment.
Similar to patching/upgrading in a physical environment, a reboot may be required as part of the
patch/upgrade process. You can use a native database high availability feature such as Oracle
RAC, SQL Server failover cluster, or SQL Server database mirroring in conjunction with a
database virtual machine to reduce downtime during updates. For example, if you implement
SQL Server database mirroring, it is possible to apply Windows and SQL Server updates
including service packs in a rolling upgrade fashion to minimize server downtime.
Regardless of the method you use to patch or upgrade your database virtual machines there is
always a risk of introducing new bugs or regressions that can take down the database server.
Uninstalling a patch/upgrade may not be an option. In the physical environment, rolling back a
patch could be a major undertaking that involves uninstalling and reinstalling the database binary
and restoring the database from the backup disk. VMware snapshot technology can save a
tremendous amount of time in these scenarios.

technology, not only to provide point-in-time restore capabilities, but to efficiently use available
disk space.
As most DBAs know, the use of a database-aware backup agent provides database health
checking and log truncation. Though VDR can use the Volume Shadow Copy Service (VSS)
framework to back up guest virtual machines it does not contain the required database VSS
requestor to properly backup and restore a database. You can write custom scripts to involve the
appropriate database VSS writer.
8.4.3 Array-based Backup Solutions
As is the case with the in-guest solutions, array-based solutions provided by many of the leading
storage vendors continue to work with databases running VMware vSphere. Array-based backup
solutions for SQL Server or Oracle use the VSS to produce near-instant, application-aware
clones or snapshots of databases. These local clones or snapshots can then be backed up to
disk, tape, or cloned offsite for disaster recovery purposes. Consult your storage vendor for
guidance on array-based backup support in a virtualized environment. DBA Guide to Databases on VMware

© 2011 VMware, Inc. All rights reserved.
Page 25 of 32
Figure 10. Array-based Backup Solution 8.5 Manage Legacy Databases
Many organizations need to support applications for extended periods of time due to regulatory
and compliance requirements, or for other reasons. DBAs are tasked to maintain those legacy
database systems. Often, legacy applications are not be able to run on newer hardware due to
vendor support and/or compatibility issues. Older hardware may fail more frequently and
equipment replacement may require more database downtime, which can adversely impact an
organization’s availability requirements and service level agreements.