Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

The pros and cons of virtualizing SQL Server environments

What are the pros and cons of virtualizing SQL Server environments? Basit Farooq takes you through the potential good, bad and the ugly.

Virtualization is a hot trend in the computing world, offering businesses substantial cost and performance benefits that include server consolidation, reducing power consumption and the effortless creation of virtual development and test environments. As a result of this trend, many organizations are interested in virtualizing SQL Server, and for a good reason. These benefits of virtualized environments increase business continuity and allow database administrators (DBAs) and IT professionals to build an agile IT infrastructure. That is why just about everyone in IT is using virtualization to some extent, although many DBAs are still not sure about whether they should virtualize their SQL Server infrastructure or what benefits they would be able to achieve by doing this.

Advantages of virtualizing SQL Server

Reduced data center foot print with database server consolidation
Production SQL Server environments can benefit from server consolidation with virtualization because it can drastically reduce the number of physical servers and minimize the administrative workloads through centralized management. With Hyper-V technology, we can host multiple virtual database servers on the same physical machine while maintaining complete isolation at the operating system level. This allows us to manage performance, security, manageability and business continuity for each virtual application server independently. It also gives us the flexibility to run multiple operating systems on the same hardware, as required by specific applications.

More on SQL Server virtualization

Getting the right hardware for SQL Server virtualization

Risks, pros and cons of virtualizing SQL Server

Is virtualizing SQL Server a myth or reality?

This means we can virtualize multiple underutilized physical SQL Servers on a single powerful physical server. As a result of this approach, we end up managing a small number of physical servers, which simplifies the administration; reduces operational, maintenance and hardware costs; and saves data center space.

Reduced licensing and operational costs
As mentioned above, reducing the number of physical database servers also reduces data center operational costs. This is due to reduced power and cooling requirements.

Furthermore, by virtualizing SQL Server environments, we can make significant license cost savings, because the number of licenses required is reduced. For example, each physical server requires licenses for the operating system and utility software. By reducing the number of physical servers, we also ensure we do not need these additional licenses.

License costs are also dependent on the software and technology used for virtualization. For example, with Windows Server 2008 R2 Hyper-V, we can eliminate the need to purchase separate virtualization software. We can reduce the license cost even further by taking advantage of the premium edition licensing. For instance, we can purchase licenses for our physical servers, and then use these physical servers to run as many instances of SQL Server within the virtual machines.

Remember, though, we can only host three virtual machines on Windows Server 2008 Standard Edition licensed physical server and an unlimited number of virtual machines with Windows Server 2008 Enterprise Edition licensed physical server.

Better manageability through centralized administration
As mentioned above, the virtualization of SQL Server environments also simplifies administration, because we can manage these virtual machines from a single location using such tools as Remote Desktop or System Center Virtual Machine Manager, which help DBAs and IT professionals manage their physical and virtual infrastructure from a central location.

Improved high-availability and disaster recovery solution
A virtualized SQL Server environment provides improved high-availability and disaster recovery features. For example, we can cluster our physical hosts running Hyper-V, back up our virtual hard disk or implement guest operating system clustering using the SQL Server AlwaysOn feature. This will ensure that our environment is protected against virtualized infrastructure failures such as SQL Server instance failure. We may also use database mirroring to protect our critical SQL Server environment from a shared drive failure.

Disadvantages of virtualizing SQL Server

Scalability and performance
Although virtualization is suitable for most situations, it is not the right solution for all situations. For example, it is not ideal to virtualize CPU or memory-intensive SQL Servers. It is also noted that virtualization requires additional hardware resources, but the problem is that it's almost impossible to estimate in advance how many additional resources will be needed. It is also observed that some SQL Servers can experience performance degradation after they are virtualized. It is advisable to properly benchmark the performance of your SQL Server in the virtual environment and then test the solution carefully before implementing it in the production environment.

Single-point failure
Fault tolerance is another issue with virtualizing SQL Server environments, since hardware failure on the host will also bring down all guests running on that host. This problem can be eliminated by clustering the hosts.

Security is also a concern in virtualized SQL Server environments. If the security of your host is compromised, the security of all guests running on that host will also be compromised.

Software support
Many software vendors still do not support their applications if you are hosting their application databases in a virtualized environment. These are all concerns to keep in mind when you consider going virtual.

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization