Guide to SQL Server virtualization best practices
A comprehensive collection of articles, videos and more, hand-picked by our editors
Editor's note: This is the second in a two-part series by Robert Sheldon on situations in which SQL Server virtualization...
might not be the best option. The first part is also available.
Some databases must be available day and night, with little or no down time. This usually means taking advantage of SQL Server's high availability features. One of the most commonly implemented is the failover cluster, which can be virtualized just like stand-alone instances of SQL Server.
Whether to virtualize your database clusters is perhaps the most contentious of all virtualization discussions related to SQL Server. Many DBAs swear that a virtual cluster is the kiss of death. Even so, plenty of others have demonstrated that virtual clusters can and do work, if issues of performance are adequately addressed. But even under the best circumstances, setting up and managing a cluster can be a complex process. Virtualizing that cluster only adds to that complexity by inserting yet another layer of resource management. Plus, troubleshooting a failed node can take longer in a virtualized environment, and even patching the VM software can often significantly impact availability. Some DBAs suggest that if you can't tolerate downtime longer than 30 seconds, you probably shouldn't virtualize.
Even if you can meet this demand, another challenge with virtualizing clusters is ensuring that your virtualized clustered nodes are not set up with a single point of failure. Each VM (virtual machine) that contains a node should be hosted on a separate server and should not share storage. Otherwise, you defeat the purpose of clusters -- or mirroring or log shipping, for that matter. If you cannot eliminate single points of failure, you shouldn't virtualize your clusters.
SQL Server Support
For some DBAs, the question of virtualizing SQL Server is not whether it can be implemented, but whether it can be supported. A common concern expressed by many DBAs is the breakdown of communication between themselves and IT, particular those responsible for the virtualization efforts. By its very nature, virtualization adds another level of complexity to your implementation. VM resources must be planned, provisioned and managed. Performance must be tested before deployment. Requirements specific to SQL Server, such as turning off CPU power saving features or monitoring utilization rates between blade chassis switches, must be taken into account. In fact, monitoring in general becomes more complicated, as does licensing.
More on SQL Server high availability
High availability options and caveats for SQL Server
High availability on the cheap: inexpensive options for SQL Server 2012
Add to all this the fact that Microsoft places a number of restrictions on how it supports virtualized SQL Server products. For example, virtualization environments must meet the requirements of Windows 2008 or Windows 2012 Failover Clustering, or Microsoft won't support the SQL Server installation. In addition, some software vendors won't support their applications if the applications' databases are hosted in a virtual environment. These issues must be vetted before embarking upon the virtual journey.
What all this points to is a need for effective communication among the participants, along with a coordinated management strategy and careful provisioning to avoid resource contention and server sprawl. In most cases, the people who implement the VMs are not DBAs and often don't fully understand the complexities of a SQL Server installation and its performance tuning requirements, instead treating SQL Server like any other server; but it's not like any other server. True, communication failures are not limited to virtualization, but if there's any doubt that your virtualized system can be fully supported, then don't virtualize it. If you have no choice but to virtualize, make sure the stakeholders know the risks and challenges involved.
Too Big to Fail
The challenge with many virtualized databases is that it can be difficult to predict exactly which resources will be required when you need them. A large database can be resource intensive, but, under the right set of circumstances, a smaller once can as well. Despite this, virtualization might prove the perfect solution for your smaller databases, and perhaps for your larger ones, too. But for your massive high-powered critical systems, you might need to stick with physical servers. What is "too big" to virtualize? That's hard to say. But if you can't guarantee that your VMs will deliver the performance and availability you need, don't virtualize. If your licensing costs outweigh the savings you gain from virtualization, you might want to back off. Same goes for support. VMs don't run themselves.
Virtualization is a great technology with proven advantages, even for SQL Server. Just be sure that if you go this route, you know what you're doing. You might decide to virtualize only your development and testing environments, or perhaps virtualize everything. Whatever you do, be sure your choices are informed and based on reality. Carefully consider what you're embarking upon before it's too late to turn back.