Posted by: Denny Cherry
SQL, Virtualization, VMware
Please forward this to the systems admin in your life that wants to take your big SQL Server, and cram it into a Virtual Machine against your wishes.
So you’ve got your kick ass virtualization project moving along nicely, but you’ve hit a snag. The DBAs are fighting back saying that they don’t want to virtualize the SQL Servers. You hear them, but you know better right? You built the hardware, you know what it can do. The SQL Server doesn’t really need all that horse power.
The problem here is that SQL Server (same goes for Oracle, MySQL, DB2, etc) is very different that applications which run on other servers. Databases love memory, they eat it up like its candy. There’s a reason for this. Its so that they don’t have to hit the disk all the time reading data. We want all the data stored up in the buffer pool (might be called something else on other platforms).
That kills the argument about need really fast disk access for the database right? Nope! We want all the data cached into the memory for reads so that when the database writes data to the disk during its checkpoint operation happens (checkpointing is when the database flushes the transaction log into the main database file) the reads and the writes don’t get in each others way. A database that sits there with 1-2 read IOs per second, it’s a light weight database. It’s a database that’s got a good amount of cache sitting up in memory the disks won’t show a hold lot of read activity. But there will be a large burst of write data going down about once a minute or so.
As an example, my production OLTP database (the database that services our customers via our website) usually has 3-4 IO going to each of the 4 LUNs that the database sits on. However when the database checkpoints there’s easily 3-4k IOs going to each of the disks (that’s 12-16k IOs at one time being written to the disks). Putting that sort of disk load into a virtual environment would cause a large amount of queuing to happen when the database flushes.
While an app server can handle that without to much trouble, most app servers aren’t handing real time user requests for data. When the database can’t write to the disks quickly enough, the CPU load on the server will start to increase as the SQL Server has to start handling more requests. Because those requests are now starting to take a little bit longer some blocking begins happening. Then more blocking, then more. Soon the users are starting to get timeout error messages.
Now don’t get me wrong, smaller database servers can be virtualized without much of an issue. I’ve got a smaller SQL Server running our Shopping Cart, Ticketing system, Microsoft CRM, and BlackBerry Enterprise Server all running in a single VM with 4 Gigs of RAM, and 2 vCPUs and it’s cranking along without issue. But this machine has very low IO requirements. Even with all that software on there during the normal business day (outside of when backups are happening) the system peaks at 20 write IOs per second and a maximum disks queue of 1 on the data volume. In other words, this system was a perfect candidate to be virtualized. As for the SQL buffer cache on this box, the numbers couldn’t be any better. The Buffer Cache hit ratio is 100% (first time I’ve ever actually seen that before) and the page life expectancy is 10.4 hours.
So, getting back on point…
Not all database servers can be virtualized. This is one type of system, where you may simply need to have some physical boxes running the databases. Start with the low hanging fruit. Infrastructure database usually have lower database resource requirements (monitoring systems, BES, Backup Solution databases, etc) and can usually be put into a VM pretty easily.
Anything that is sitting around and isn’t actually in use on a regular basis, is another great thing to virtualize. One company I was at had a SQL 6.5 server sitting around so we could dump the data as needed for legal. The server was about 10 years old (it was 2005 at the time) and the machine was running NT 4.0 Server with SQL 6.5 with 256 Megs of RAM I think, maybe 512. We P2Ved the server into a VMware solution and powered down the host. Pulled a 10U server from the rack, saved a ton of power and cooling, and no one cared because the machine was used maybe once a year, if that.
So again, getting back on point (for new readers, you can see that I get off topic a lot some times)…
Work with your DBAs. Explain to them the goal of the project (and tell them more than we are going to virtualize everything). But you should keep in mind that some of your database servers just aren’t going to be a good candidate for virtualization. As soon as the DBAs and the Sys Admins come together and decide which servers are good candidates and which ones aren’t everyone will be a lot happier.
Something else to keep in mind, is that after you move the SQL Servers to a VM, you may need to move them back if it turns out that moving them into a VM wasn’t the right call after all. Virtualization is a tool, and just like any other tool there are times when it is the correct tool, and times when it isn’t the correct tool.
Now sysadmins, if you need help convincing your DBAs that moving some of your smaller SQL Servers into a virtual environment, kick this back to them. Remind them that some things can be virtualized, and the same metrics that show that larger systems can be virtualized can and should be used to show that smaller, lower resource systems can be virtualized.