Guide to SQL Server virtualization best practices
A comprehensive collection of articles, videos and more, hand-picked by our editors
Virtual servers have been around for several years and the technology has matured a lot. A few years ago, hardcore...
database administrators (DBAs) were extremely reluctant to migrate SQL Server installations to virtual machines, but these days going virtual is not as farfetched. There are, however, important things you need to consider before you decide to go virtual.
The most popular solutions for virtualizing SQL Server are VMware and Microsoft's Hyper-V. You should first decide which you would like to adopt. VMware is still king of this space -- it's been around longer, is a more mature product and offers more features. However, Hyper-V is catching up pretty fast and costs less than VMware. Both products offer free trial versions and, if you are just getting started with SQL Server virtualization, I would recommend that you try both.
After you determine which features benefit your company and examine how well they work, you can compare cost and performance and then decide. The SQL Server development team worked closely with the Windows and Hyper-V teams to improve performance for SQL Server and Hyper-V. If you are looking at virtualizing just SQL Server, Hyper-V is a good choice for most cases. But if your company is virtualizing other servers like Web servers or domain controllers and your IT staff's experience lies heavily with VMware, you might be better off sticking with that technology for the sake of uniformity.
More on virtualizing SQL Server
Virtualization of SQL Server is not always a good idea
Is SQL Server virtualization a myth?
Next to consider is what servers will undergo this SQL Server migration. Prime candidates are nonproduction SQL Servers. Many companies have different names for these environments, but typically they would be "development," "QA," "staging," "testing," "benchmarking" or "stress testing." More often than not, ultra-high performance is not the highest priority, which makes virtualizing them easier. Moreover, servers in these environments tend to be old, often-retired production machines with a questionable amount of lifetime left and inconsistent configuration. Also, they often lack sufficient space to restore production databases. The other sad but common occurrence is that DBAs are so overworked that they need to focus on the production environment and often end up neglecting nonproduction environments. This can result in losing databases during the development cycle due to old hardware failing or other issues, costing you development time. If you virtualize these environments, you will run on new hardware and you can make them uniform in configuration, making it much easier for DBAs to work with them and navigate around them.
So what about production servers? The answer is simple (or perhaps not so simple, which is why so many things in the world of databases require multiple considerations): It depends. Based on my experiences and discussions with other SQL Server DBAs, the consensus is that going virtual should not be an issue for small and medium workload servers. The technology has advanced enough so that virtual machines can share memory and disk I/O with SQL Server. You can consider using VMware or Hyper-V for servers with a heavy workload, but in this case I would consider doing some benchmarking or stress testing first to make sure that the overhead of running the virtual layer doesn't push it to the point where it slows performance to unacceptable levels. You can also use virtual as your initial setup in scenarios where you launch a database application but are not exactly sure whether it will be successful. Over the years, I've experienced several cases where we prepared for the worst, built a super-powerful server with hard disks and launched an application, and it did not exactly take off as planned.
The one area where I would still advise you to be cautious is business intelligence servers. These servers often host very large databases and they tend to need very fast processing. When online analytical processing (OLAP) cubes are built, SQL Server may need to scan terabytes of data. In this scenario, you may need some type of "pedal to the metal" performance, so the overhead of virtualization may be unacceptable. In my company, for example, we did not see good performance on a virtual data warehouse server and needed to switch back to a physical server.
High availability is very important these days, and this is one area where virtualization can help a lot. For example, VMware has a technology called "vMotion" that allows a virtual machine to be moved between physical servers. This allows you to keep a virtual machine up and running while you perform hardware upgrades by simply transferring the virtual machine to another physical server. Or, if the database application gets busy and the current physical server is no longer sufficient, you can, in a matter of seconds, move the virtual machine to a different physical server. And finally, if the underlying physical hardware fails, vMotion will automatically transfer the control of the virtual machine to a different physical server. So, if you set up your SQL Server on a virtual machine with vMotion, the availability is greatly improved; if the underlying physical hardware fails, vMotion will quickly move the virtual machine to a different physical node and your SQL Server remains up and running.
As you can see, considering virtualization in your SQL Server environments is becoming increasingly possible as new features are added to virtualization software, while the overhead of going virtual is getting lower.