This content is part of the Essential Guide: Guide to SQL Server virtualization best practices

SQL Server virtualization more than a passing trend?

Virtualization may be taking data centers by storm, but what’s behind the trend? In this month’s “SQL in Five,” asks Microsoft’s Mark Kromer about the benefits and difficulties of virtualizing SQL Server.

For years, the term virtualization has been tossed around as a way to get businesses more from their IT budgets and resources. But virtualizing SQL Server did not always have the best results. Now, as the technology improves, many experts are predicting that SQL Server virtualization will become the norm.

In this month’s “SQL in Five,” asks Mark Kromer, data platform technology specialist at Microsoft, to make sense of the shift from the physical-server world to the virtual one.

These days data center administrators are insisting on virtualization. What’s behind that?

Mark Kromer: There is an overall unmistakable trend toward virtualization, consolidation and “private cloud” or data center optimization. The value proposition of a virtualized data center has existed for a number of years. The savings in terms of both capital expenditures as well as operational costs makes for a very compelling business case. Saving hardware and maintenance costs, power and energy, and the savings in consolidation to simplify administration have always been very compelling to IT decision makers. It is the lack of an industrial-strength, enterprise-ready hypervisor with support from both the hardware vendors as well as the software vendors that prevented the virtualized, optimized data center from taking off.

And let’s be frank about the effects of the economic recession that hit the global economy in 2008. The economy raised the requirements to consolidate and virtualize to the top of the CIOs’ [chief information officers’] lists. Business intelligence, for example, became a “nice to have” and moved down on the priority list, with virtualization now at the top. So SQL Server falls into that virtualized infrastructure. Now that hypervisors like Hyper-V and VMWare work very well with specialized chipsets and network cards, techniques like offloading and address translation to improve I/Os make virtualization of SQL Server a viable and growing option for database deployment and provisioning.

What makes SQL Server a good candidate for virtualization?

Kromer: SQL Server is a great target for virtualization because it has this legacy of suffering from what some IT professionals refer to as SQL Server sprawl. That is, since SQL Server is very inexpensive and easy to deploy, off-the-shelf applications will install a single instance of SQL Server Express or developers will stand up a single instance for each app or each project. In the past, DBAs [database administrators] and IT shops have undertaken painstaking projects to inventory, reconcile and consolidate SQL Server instances typically by jamming databases into single instances or at least running multiple instances on a single server. Each consolidation technique comes with pros and cons.

But the simplest, quickest and most productive way to consolidate SQL Server 2008 is to create a single-instance VM [virtual machine]. There are separate configuration guidance white papers for Hyper-V versus VMWare. But once you start standing up VMs of SQL Server, you then create a “database factory” that you can better control, grow and shrink, and then eventually get to the point where you point automation around user-defined provisioning models which act as templates for your virtualization software. That’s how you get to the “private cloud” or “optimized” data center.

What benefits can companies expect to see from SQL Server virtualization?

Kromer: Some of the benefits that you will start to see in a virtualized SQL Server environment include lowered licensing costs. SQL Server processor licensing with the 2008 R2 Enterprise Edition allows up to 4 VMs per license, if you buy a license for all physical processors on the box. The Datacenter Edition goes one better: unlimited SQL Server VMs if you license the entire box. You will gain the additional capex [capital expenditure] and opex [operating expense] savings with fewer physical servers.

It is also helpful in eliminating standalone servers for those SQL Server 2000 or 2005 instances that are spinning just because the application vendor does not yet support SQL Server 2008. Just virtualize them and stick them on a server with other SQL Server 2008 R2 VMs. Another nice added benefit of using a hypervisor like Hyper-V on Windows Server 2008 R2 is that SQL Server 2008 R2 supports live migration. This is the ability to move VMs around from server to server with zero downtime, providing you with high availability. You can still use Windows Clustering on the VMs and database mirroring. But these load-balancing techniques that Hyper-V and VMWare provide give you optimal use of your hardware instead of active-passive scenarios. Once in the virtualized world, you can start to leverage economies of scale that come along with this change, such as automatic server provisioning and workflow that can determine the best-fit database size and host location based on the user’s requirements, preferably coming from a webform that the user has filled out for you.

What difficulties could they face?

Kromer: There are several things that you face that may seem like a challenge. First, for traditional data center admins and DBAs, moving into the world of virtualization is a mindset change. You are no longer tying databases or applications to a physical host. To get the best return on your investment with virtualization, you’ll let the VM management software load balance the machine, and provision and move VMs around to the least-used host or to the proper host based on your configured business rules. It takes a little getting used to, not knowing at all times where something is physically located. You’ll also want to become familiar with managing VMs from a system administrator’s perspective. Become familiar with the management and monitor tools associated with the hypervisors. And from a DBA’s perspective in terms of tuning the database, you have to accept that you’ll find yourself CPU-bound by the limitations of the number of CPUs that the host OS can present to the hypervisor and that a server will also need to divide the physical memory and I/Os to a number of other VMs also running on that server.

What should be on companies’ to-do lists before virtualizing SQL Server?

Kromer: For SQL Server, the first thing to do is to evaluate virtualization server tools for your enterprise. For Microsoft Windows Server 2008 R2, for example, Hyper-V is included as a way to create VMs with a full “guest” operating system (usually another Windows Server) running SQL Server 2008 R2. Once you’ve decided on your VM software, you’ll need a complete inventory of your entire SQL Server environment with versions, editions, location, purpose, et cetera. The best way to do this is with the Microsoft free download tool called SQL Server Consolidation Toolkit, which will give you recommendations and next steps from the MAP output about consolidating your SQL Server instances. Now, before moving forward with a virtualization strategy, make sure that you fully test each scenario on a test or staging box. You are likely to see differences in the DMVs on your SQL Server VM from running SQL Server native on the host OS. This is because the I/O is a little different and there is a limit to the number of CPUs that you can present to VMs. Lastly, if you are virtualizing a COTS [commercial off-the-shelf] or third-party application, or even just the database for that app, check with the manufacturer first to ensure that they support virtualizing the database and their application so that you are sure that you will run in a supported mode.

Editor’s note: For more from Mark, check out his blog at MSSQLDUDE.

Mark Kromer has over 16 years experience in IT and software engineering and is well-known in the business intelligence (BI), data warehouse and database communities. He is the Microsoft data platform technology specialist for the mid-Atlantic region.

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization