Not If But When: Best Practices for SQL Server Virtualization

For SQL Server DBAs, virtualization is no longer a question of if but when, even for mission-critical applications. Virtualization introduces some new challenges for the DBA, starting with developing a working knowledge of the virtual infrastructure and how it interacts with the database environment. We recently explored these issues with a panel of SQL Server and virtualization experts on a Twitter datachat (#datachat) to learn what lessons they have learned along the way.

In this article, we’ve captured the best parts of that discussion with Denny Cherry (SQL Server MVP and founder of Denny Cherry and Associates), Karen Lopez (SQL Server MVP and Sr. Project Manager / Architect at Infoadvisors,), David Klee (SQL Server and VMware expert and founder at Heraflux Technologies), Daniel Taylor (Senior Database Support Specialist at New York Life Insurance), Tyler Krusenstjerna (Database Administrator at Pearson VUE), Allan Hirt (SQL Server expert, Cluster MVP and partner at SQLHA) and Thomas LaRock (SQL Server MCM and MVP, and Technical Evangelist at SolarWinds).

Which SQL Server databases are getting virtualized and why?

Although in the past, size and workload have often been reasons to not virtualize, this is no longer the case. With regard to size, even very large databases are being virtualized now, as Denny Cherry and David Klee have both experienced:

 

In fact, the only real limitation is in the limitations of the host or hypervisor, as Thomas LaRock notes here:

Denny Cherry agrees that only SQL Server databases with CPU or memory requirements that exceed the limitations of the hypervisor or mission-critical workloads with extremely high transactions per second (TPS) will likely not be good candidates:

Other than the technical limitations of the virtual infrastructure, a very real reason that impacts the decision to virtualize is a lack of tolerance for risk or perceived risk. As Cherry goes on to note, this perception of risk can put an end to any virtualization initiative:

Daniel Taylor goes on to note that the perception of risk can limit virtualization projects:

But as we’ve seen, with adequate planning and design, there’s no technical reason not to consider virtualization.

 

What are the challenges for performance tuning for SQL Server databases in a virtualized environment?

From the database perspective, there is little difference between running a SQL Server database instance on a physical server or a virtual machine (VM). From the DBA perspective, virtualization introduces a few new challenges. Often, it’s difficult for DBAs to gain visibility to what is happening in the virtual environment:

Just as with a physical server, you need to be able to identify the root cause of performance issues, whether they exist in storage, the host, the guest or inside the database:

 

DBAs must take the initiative to learn about the virtual infrastructure and understand how it impacts the database. Denny Cherry encourages DBAs to learn as much as they can about the platforms on which the database is running:

 

Developing a good working relationship with the virtualization manager is also a very good idea.

 

What tools should I use to manage SQL Server instances in a virtualized environment?

Once you understand the virtualization environment, you’ll need additional tools to get insight into how that environment is impacting your database. Some tools, native to VMWare and Hyper-V, contain data specific to virtual performance, and as Thomas LaRock commented, is well worth learning to use:

Yet, as Denny Cherry observes, getting access to tools native to the virtualization environment presents a challenge for many DBAs, as does developing skills in using them. Cherry goes on to note that third-party tools such as SolarWinds Database Performance Analyzer VM Option can help bridge the gap between vSphere and SQL Server:

 

David Klee echoes the idea that a DBA should assemble a variety of tools to best manage the SQL Server database instance in a virtualized environment, including native tools like VMware vCenter:

Whatever tools you choose to use, they should enable you to see from the database all the way through the virtualization stack, and help you correlate database performance with physical and virtual resources.

 

What causes the biggest bottlenecks with virtualized SQL Server databases?

When it comes to what causes the biggest performance bottlenecks for SQL Server databases that have been virtualized, Denny Cherry reminds us that the most common bottlenecks in a virtualized environment are frequently the same as in a physical environment:

LaRock points also to storage as a strength and weakness for virtualization and a significant source of database performance issues:

Denny Cherry and David Klee both agree on the issue of storage, and add that overcommitted CPUs on hosts are issues they frequently encounter:

 

Not knowing the real requirements for the VMs poses an additional challenge and can lead to performance issues, as Allan Hirt points out:

Over time, changes in the data environment, such as unexpected growth, can also cause issues like a VM outgrowing the host, perhaps by just one metric, like CPU or memory:

When a SQL Server database is virtualized, pinpointing performance requires tools and knowledge that enable a view from the database all the way through to the virtual resources. It’s important that the DBA work closely with the teams managing both the physical and virtual environments to understand the impact on database performance.

 

Have no fear–you can virtualize SQL Server with confidence

When you take the time to develop a solid knowledge of the virtualization environment and access to the right tools to see the correlation between SQL Server performance and resources in the virtual environment, you should be confident that you can make the most of what virtualization promises while maintaining optimal database performance.