By: Scott Shaw | Updated: 2011-08-18 | Comments | Related: 1 | 2 | 3 | 4 | 5 | > Virtualization
Problem
Your first tip provided a quick overview of VM terminology and your previous tip talked about why and when I should virtualize SQL server. We are now ready to begin the process of virtualizing SQL Server. What are some questions I should ask? Are there certain things I should know before hand to help with the transition? Check out this tip to get your questions answered.
Solution
You may be working in a large shop with 100's of SQL Servers or you may be a jack-of-all-trades DBA in a small shop with only a few SQL Servers. The implementation may differ, you may be the implementor or others may be doing the implementing, but you'll still need to ask the same questions.
This tip attempts to hash out these questions. I'll attempt to answer some of them, but the devil is always in the details. Unfortunately my experience is hindsight. It's been learn-as-you-go, but the most valuable lesson I've learned is what questions I should have asked.
The Questions
The following are a list of the quick and dirty questions you should ask. Feel free to comment on this tip if you can think of more.
-
What version of VMWare ESX are you using?
-
What SAN storage will you use?
-
What speed is your SAN connection?
-
Are you implementing storage tiers?
-
How will you be converting to VM?
-
Who do I contact for performance problems? Who do I contact if I need more resources allocated?
-
Will I have access to vSphere (or other VM monitoring tools)?
-
How will you determine initial VM resources?
-
How will virtualization affect SQL Server licensing?
-
Do you require implementing a hardware backup solution for SQL Server?
-
What settings will you enable? Will you be implementing thin provisioning? Will you over provision? Are you planning to turn on the balloon driver?
-
What are the host hardware specs? Are they significantly faster than the current physical specs?
I don't think it is necessary to cover all of these, but some of them bear some explanation. Keep in mind one point. In my experience, when virtualizing servers there is a tendency to mix virtualization with SAN implementation. In fact, the two are inseparable and go hand-in-hand. Yes, there are things to keep in mind specifically about the virtual environment, but understanding and successfully implementing a SAN storage solution is critical to a successfull virtualization project. This is why some of the question lean more toward storage than virtualization.
The Answers
The first question is a no brainer. If they are not implementing anything, but ESX 4.0 or greater then you should run the other way. Changes to the CPU scheduler and also the amount vCPU allowed to each VM greatly improved in version 4 (read about the scheduler here ESX 4 CPU Scheduler). Version 5 was recently released and supports up to 32 vCPUs per VM.
The next 3 questions pertain to the SAN. First off, know the hardware. For example, are you using EMC or NetApp? You want to know the hardware because you'll need to know the product. To know the product is to know your options. EMC offers storage tiering which allows you to optimize disk for usage. Kind of like QOS (quality of service). You might have a range of disks to use from fast SSD to slower SATA. In any case, you have the right to know on what technology your databases files are living. In the physical world you'd want to know speed of the local disks so why not also come to terms with the speed of the SAN. Nothing less than 10 GB iSCSI should be used. As always, faster the better.
Think about how you will convert to VM. Will you rebuild from scratch or will you migrate? 90% of our conversions where P2V's (physical to virtual) migrations. This works if you are not interested in upgrading any of the software. You'll still be able to take advantage of the faster host hardware, but if your original system was running SQL 2000 in the physical environment it will still be running SQL 2000 after the P2V. There are cases where you'll want to use the physical to virtual conversion as an excuse to also upgrade the software.
Think about licensing. There is a link pointing to Microsoft's licensing guide for SQL Server 2008 R2 in the Next Steps section. Included in the guide (and shown here) is a quick and dirty calculator for running editions on VM that are not Enterprise or DataCenter. Most likely you'll be licensing per vCPU. A vCPU license is always calculated in relationship to the number of physical cores.
Many of the questions concern procedural aspects. They ask about how things will be handled when things go wrong. You need to start thinking about the division of labor and control. You will have to have access to the tools monitoring the virtual environment. In the case of VMWare, vSphere is the tool you'll need. You only need read access, but will most likely need the client installed on your workstation. Apparently version 5 may have a web based version, but I haven't used it yet. You'll also need points of contact for VM related issues. I constantly ask our VM team to double check performance analmolies. If I see high host disk latency I may ask them to double-check for me. A good working relationship with the VM team, storage team, and\or server teams is critical to managing the environment.
One reason for a good relationship with the teams is they will most likely be the ones determining the features to turn on or off. Will they over provision each system and, if so, do they have capacity for the overflow? If they decide to turn on the balloon driver than you'll need to make sure "lock pages in memory" policy is not given to the SQL Server service account. Other things to consider include any new backup processes like snapshots. Many hardware vendors also supply backup solutions and they'll pitch these to your storage and vm administrators. As a DBA you may or may not be brought into these converstations so don't be suprised when the admins approach you and ask you to start backing up SQL Server with products like Syncsort, SnapManager for SQL Server, or Avamar. Trust me - in most cases you'll want to push back on this.
Lastly think about how the systems will change after the migration. Will resources be reduced? Will they be increased? If resources on the VM will be reduced how will that be communicated to customers and who is in charge of provisioning more resources if needed? Resources may, in fact, be reduced during the P2V because in most cases the memory and processor is much faster on VM hardware than on the physical machine. Though you are virtualizing an older server running Windows 2003 and SQL Server 2005 that software will be virtualized onto new host hardware. It wasn't unusual in my shop to cut servers from 16 CPU down to 4 or cut memory in half. The important part is to make sure you or another team accurately determines how much resources virtual machines need. VMWare offers Capacity Planner software which monitors a system and will tell you how much memory and CPU it will need in the VM environment. It isn't full proof so you'll still need to make sure you can get resources added when you need them.
Summary
This certainly isn't an exhaustive list of questions to ask or problems you'll encounter. I would love to hear what your experiences have been and any questions you have asked or plan to ask that I didn't cover. These are high-level questions and concepts. You'll need to work closely with your teams and management to hash out the details. A lot of companies rush virtualization and that's never a good approach. Anyone who has worked for a large to mid-size company that has gone through virtualization will tell you that virtualizing your systems is a systemic change to your environment. It changes how you do your job and it changes the dynamics between teams. Virtualization is as much as a technical change as it is an organizational change. Make sure you know beforehand how to do it right but, more importantly, make sure you know what to do if it goes wrong.
Next Steps
- One of the best storage primers I've had the pleasure of reading Storage Review
- DBA Guide to VMWare from VMWare DBA Guide To Databases on VMWare
- VM licensing for SQL Server 2008 R2 SQL Server licensing quick reference guide
- Exhaustive paper on SAN deployments for VMWare virtual machines SAN System Design and Deployment Guide
About the author
Scott Shaw is a Lead SQL Server DBA with extensive experience running SQL Server in a virtualized environment.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-08-18
