Strategies and Best Practices for Virtualizing SQL Server

Kevin Kline

Published On: October 31, 2019

Categories: SQL Server, Hyper V, Virtualization 0

Virtualization is an expectation today, including for mission-critical systems using SQL Server. In its first decade of implementation, virtualization was rare for database workloads. However, virtualization is largely defacto across medium- and enterprise-sized business in the USA and around the world. Despite widespread adoption by more than 60% of medium and big business, some businesses remain nervous about taking on a SQL Server virtualization project, largely because of performance concerns and unfamiliarity.

But we’re here to help! With proper planning, use of best practices, and leveraging a database performance monitoring solutions such as SentryOne can help organizations ensure quality performance in virtualized SQL Server estates.

Context

This blog was adapted from a webinar I participated in with Heraflux Technologies Founder and Chief Architect David Klee, and SentryOne VP of Client Services Jason Hall. In the webinar, we shared strategies and best practices for virtualizing SQL Server to ensure high performance and easier troubleshooting.

Key Takeaways

Server Consolidation Is the Top Reason Companies Virtualize Systems

Server consolidation is the biggest draw to virtualization, as companies realize they can reduce their server overhead to save money on power, cooling, space, networking, cables, and even the hardware warranty.

Virtualization also provides organizations a disaster recovery (DR) solution, the ability to continue support for legacy applications, and a quick and easy way to create test, development, and training environments.

Virtualization does have drawbacks, which can include overhead expenses from licensing, complexity around system administration, and challenges with performance monitoring and tuning.

Pros and Cons Virtualizing SQL Server

David Klee warned that when addressing performance issues, it is important to look across the shared virtual environment—not just at the database—to determine what might have changed that is impacting database performance.

Proper Rollout Planning Identifies Virtualization Needs

Rolling out a virtualized environment requires proper advanced planning. Important areas of consideration include:

  • Server use—Categorize and profile the workloads to understand what the environment should be and what resources are needed.
  • Workload and performance—To define system sizing, capture metrics and understand how systems are used, when workloads peak, and what the performance expectations are. Determine whether existing hardware can be used to host virtual machines (VMs) and support the workload with the expected performance.
  • Service Level Agreements (SLAs)—Ensure SQL Server and virtualized SLAs are set up to complement one another, and that teams involved in supporting the systems are set up to meet those SLAs so that costly downtime—planned or unplanned—is kept to a minimum.
  • End users—Make sure administrators and other end users are trained and set up with practices and processes that allow them to support the virtualized environment.
  • Availability and recovery—Understand expectations and ensure that SLAs and processes are set up to properly support the need, including for high availability (HA) systems.

Licensing Is Complex, But Needs to Be Done Right, Especially When Virtualizing Systems

Microsoft licensing—especially when it comes to virtualized systems—is complicated. A consultant can help organizations wade through the licensing permutations, potentially saving tens of thousands of dollars by selecting just the necessary licenses.

Licensing Considerations Create Complexity
  • SQL Server editions (standard/enterprise)
  • Standalone versus clusters
  • Single instance (operating system environment) versus hosts
  • Passive (with a time limit) versus working servers

Klee recommends purchasing a Microsoft Software Assurance (SA) agreement, which provides VM mobility and the necessary flexibility for virtualized systems. He also suggests looking into host core-based enterprise licensing if scale matters, since consolidation of licensing and keeping the VM footprint to a minimum can save money.

Licensing for passive servers, like those typically used for DR scenarios only, is different from licensing for working servers. A properly constructed SLA, as well as proactive auditing and monitoring that shows how frequently the server is at work, helps ensure the organization is in compliance.

Follow Best Practices to Optimize Virtualized System Performance

Modern virtualization tools are highly efficient; when best practices are followed, the performance overhead isn’t noticeable.

The speakers shared several best practices recommendations around implementation—both for Hyper-V and VMware—storage, and performance monitoring.

Virtualization Best Practices 
Universal recommendations
  • Use recent hardware from trusted vendors; don’t try to cut costs by using cheap hardware or older systems lying around.
  • Use the right network and device drivers for the virtualized system; most needed drives are built in.
  • Never over-commit memory when running SQL Server in a VM; overcommitting central process unit (CPU) is fine as long as it is monitored.
  • Avoid pass-through disks on the guest VM where possible, especially when running traditional SQL Server clustering with shared storage.
Storage
  • Don’t use Dynamic virtual hard disks (VHD).
  • Use dedicated storage disks.
  • For hard disk drives (HDD), use fixed-size VHD/VMDK (virtual machine disk).
  • For solid-state drives (SSD), size can be expanded, but the VM must be shut down first; SSDs are treated as thin provision by the array.
Performance monitoring

Regular performance monitor (PerfMon) counters don’t give a complete picture of what is happening in the VM. However, you need to monitor at least these basic PerfMon counters:

  • Hyper-V Hypervisor Logical Processor
  • Hyper-V Hypervisor Root Virtual Processor
  • Hyper-V Virtual Processor
  • VMware CPU Usage

SentryOne V Sentry Allows You to See What Is Going on in the Virtualization Layer

As a skilled DBA, you know that you need to ensure that SQL Server’s performance meets your customers’ expectations, not just in the database server itself but also in the platform upon which it runs. And if performance is not meeting expectations, you need tools that allow you to see where the problem might lie.

SentryOne V Sentry allows you to monitor SQL Servers from the virtual host to the guest VMs to SQL Server itself, and to look at key information normally available only from the virtual host.

SentryOne V Sentry dashboard

V Sentry dashboard helps DBAs quickly pinpoint which VMs might be contributing to performance issues

Using V Sentry, DBAs can resolve virtualization-centric problems faster than ever and, in many cases, even detect issues before they become problems.

The V Sentry dashboard displays metrics that are relevant to the host at an aggregate level, as well as at the individual VM level, including network, CPU, system memory, and disk input/output (IO). This helps you quickly pinpoint which VMs might be contributing to performance issues.

Silence Those Noisy Neighbors!

The “noisy neighbor” effect refers to a condition that occurs in virtual and cloud computing environments where a single tenant of a given host server monopolizes bandwidth, disk I/O, CPU and other resources to the point that it negatively impacts the performance of other tenants of the host. The noisy neighbor effect causes other virtual machines and applications that share the host infrastructure to suffer from poor performance. Noisy neighbors are notoriously difficult to detect and diagnose. But SentryOne V Sentry is designed to reveal noisy neighbors in a single click.

V Sentry also provides a view into disk activity and disk space for both VMware, which looks at data stores, and Hyper-V, which looks at the disk subsystem level in Windows.

SentryOne V Sentry disk activity

V Sentry provides a view into disk activity and disk space for both VMware and Hyper-V

Take V Sentry for a Spin

Virtualizing SQL Server environments offers numerous benefits to organizations, and V Sentry can provide visibility into one of the few performance black boxes you might encounter, giving you the information you need to identify and address performance issues.

Interested in trying SentryOne V Sentry out for yourself? Download a free trial today.

Kevin (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.


Comments