NESQL 2010 October 14 Virtualizing SQL Server

I attended a New England SQL Server User Group meeting last night. Brent Ozar did a presentation on Virtualizing SQL Server.

During the last few years, I have become a fan of virtualization for SharePoint environments, including the SQL Server database server role. I know many in the SharePoint community are skeptical about this, so, it was interesting to observe the opinions and responses from the local SQL Server community. I think the majority of the room was still skeptical (as expected) about virtualizing SQL Server. However, Brent did a good job of providing guidance on how to get the best performance out of a SQL environment when it is virtualized.

Beyond enlightening me about some of the finer details of how virtualization impacts I/O, Brent's presentation provided greater validation for my opinion of virtualizing SQL Server. At this point, I believe the technology is definitely mature enough to handle all roles (including database role) of most production SharePoint environments. That is; if adequate hardware is allocated and if virtualization is implemented well.

Some of the take-aways I scribbled down include the following (no warranties with these notes because I can barely read what I wrote):

CPU
- CPU-Z is a great, free utility for monitoring true CPU performance on guest systems
- In a virtual environment, more cores assigned to a SQL Server VM may actually leads to worse performance due to overhead related to instruction assignment behavior in shared environments relative to processor caching.

RAM
- In VMWare, set memory reservations, don't over commit RAM, use large pages (Microsoft, 2010).
- In SQL Server, remember to set min/max memory appropriately.

Storage
- Group DBs logically based on purpose, maintenance schedule, security, etc.
- Still separate Temp DB in virtual environments.

I believe Brent will be presenting the complete version of his presentation at the PASS Summit in November. Either way, if you are reading up on this topic, then check out his blog post on Virtualization Best Practices.


Reference

CPUID (2010). CPU-Z supports the new Intel Sandy Bridge processors. Retrieved October 15, 2010 from
http://www.cpuid.com/news/26-cpu_z_supports_the_new_intel_sandy_bridge_processors.html

Microsoft (2010). SQL Server and Large Pages Explained. Retrieved October 15, 2010 from http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx.

Ozar (2010). Virtualization Best Practices. Retrieved October 15, 2010 from http://www.brentozar.com/sql/virtualization-best-practices/.

Ten Reasons Why I Love VMWare for SQL Server

Sometimes it seems as if there is a "do not virtualize SQL Server" echo in the SharePoint community, at conferences, and events. For many practical reasons, I reject this sentiment and believe that SQL should be one of the first roles to virtualize.

Here's ten quick reasons:

1. Storage - SAN storage, local drives or ISCSI drives. Storage can grow as requirements change.

2. Performance - Yes, SQL Server requires a lot of resources, but if you use resource governor in SQL Server 2008 and later you can control a lot of the spikes. In a virtualized environment, the pool of shared resources provides good spillover coverage for member servers.

3. HA - VMotion. Enough said. Sure, you can still leverage SQL native HA features such as clustering, replication, log shipping...but hardware failures occurring at a local site can be covered seamlessly with VMotion and require far less configuration at the DBMS level.

4. VMWare Snapshots - Right-click and snapshot before making configuration changes. Big sigh of relief for QA processes.

5. VMware Templates - Build an operating system, template it, re-use it over and over again.

6. SAN Snapshots - Many of the current storage arrays, such as Dell EquaLogic, ship with snapshot capabilities.

7. Centralized Management - Yeah, I like being able to access the whole farm from one console and monitor what is going on, quickly remote into systems.

8. Disaster Recovery - Advanced features to help facilitate remote site recovery. Several more layers of data and configuration protection than physical environments.

9. Physical to Virtual - Drivers aren't an issue, capture and protect important systems that happen to be running on legacy hardware.

10. Little Conveniences - How about an ISO drive where you can store and back up all of your ISO images for operating systems, software applications, etc.