Virtualizing SQL Server: Strategies and Best Practices
Published On: October 31, 2019
Categories: SQL Server, Hyper V, Virtualization 0
SQL Server virtualization is an expectation today, including for mission-critical systems. 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.
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.
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.
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|
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.
|SQL Server Virtualization Best Practices|
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:
SQL 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.
SQL 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.
SQL Sentry dashboard helps DBAs quickly pinpoint which VMs might be contributing to performance issues
Using SQL Sentry, DBAs can resolve virtualization-centric problems faster than ever and, in many cases, even detect issues before they become problems.
The SQL 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.
SQL 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.
SQL Sentry provides a view into disk activity and disk space for both VMware and Hyper-V
Take SQL Sentry for a Spin
Virtualizing SQL Server environments offers numerous benefits to organizations, and SQL 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 SQL 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.