SQL P2V: What Really Killed the Dinosaurs

Say we have a physical database server sitting over there in the rack.  For the sake of discussion, let’s pretend it’s a slightly older model, a HP DL380 G2 with a couple of P3 CPUs, 4gb of ram, and six local 36gb drives.  The storage is broken up into a mirrored pair of drives for C (the OS), and a four-disk raid 5 for the data and the logs.  (No, that’s not best practices, but it was built before we came to work here, and you know how that last DBA was.)

It serves the databases for our help desk software.  It’s a dedicated machine with no high availability solution: not only is it not clustered, but we don’t even have another DL380 G4 in the shop that we could replace it with if we had a hardware failure.  If this box goes, we’re in trouble: we have to drop everything and rebuild it with whatever hardware is lying around the shop at the time.

To make matters worse, it’s SQL 2000, and it has some applications installed on it.  It’s got some kind of help desk service on it that does something to the database, and nobody knows exactly how it works.  We try not to look directly at it, because we’re afraid it will crash, and we’ll be out of luck.

It’s a dinosaur, and we need it to go away.

In A Perfect World, The Dinosaurs Don’t Fight Back

In a perfect world, we’d buy a new server, transition everything to SQL 2008, get the help desk vendor to do the installations, get the help desk group to test the application, and have a controlled, orderly migration over to the new platform.

How often does that happen?

All that costs money, and all that takes time - two things we can never seem to keep around the shop.

The help desk department won’t put any money in the budget because they’re happy with the performance.  They don’t need it to go faster, they don’t need high availability (because SQL just works, right?) and they don’t want you to touch it because it might break.

We DBAs want to make sure that if the hardware fails (because sooner or later it will) we’ll be okay, and we won’t have to drop everything to rebuild something we don’t really understand.  Unfortunately, we can’t get the budget money either, and even if we get the money, we don’t always get the time.  We need the process to be seamless and fast, and maybe even nearly free.

Enter the Physical to Virtual (P2V) Process

If you haven’t done a P2V migration before, you’re going to think I’m crazy.  You’re going to call this stuff a big fat lie, a bunch of vaporware that can’t possibly work.  I know: I thought the same exact thing before I started doing it a few years ago.  I still get a big grin on my face every time it works, because it’s such cool technology.  As you read through the next couple of paragraphs, just bear with me and trust me - it really does work.

The process goes like this:

• Take a backup of the dinosaur
• Shut the dinosaur down
• Create a new virtual machine in a VMware ESX or Windows 2008 Hyper-V farm
• Restore the dinosaur’s OS and data to it
• Power on the new virtual dinosaur

By now, probably half of you have called BS.  The other half of you are about to call BS when you read this next sentence: the new virtual dinosaur is going to need a completely different set of drivers for the virtual server’s storage, networking and video.  Wow.  How often have you been able to pull hard drives out of one model of server, slide them into a completely different model of server, and have everything work correctly?

Back in 2005 when I started doing this, it was rocket science, and it was scary.  It went wrong more often than it went right, and I earned a lot of my gray hairs on lonely weekend nights in the datacenter.  However, even when it goes wrong, it’s still not that bad, because we have our original physical server standing by.  If the P2V process doesn’t work, we just power the physical server back on, and the data is untouched!  Things keep chugging along the way they were, and we can try the P2V process again in a couple of weeks after we’ve recovered.

You, dear reader, are lucky.  You have the good fortune to not be an early adopter the way I was, and you can buy a package off the shelf to do this stuff - or just download one, because there’s good free ones out there too.  Search for P2V, and you’ll find a ton of reviews.  (Disclaimer: the company I work for makes one of the products, but I’m not doing marketing today - just talking about your options.)

These solutions take P2V to the next level.  Here’s how easy they make the process:

• Create a new virtual machine in a VMware ESX or Windows 2008 Hyper-V farm
• Install the agent on the current physical dinosaur
• Shut down SQL Server (but leave the OS running)
• Take a VSS snapshot of the dinosaur’s drives
• Copy the data over the network to the new virtual server
• Shut down the old physical dinosaur
• Power on the new virtual dinosaur

The cool part is that since they’re installed while Windows is running, and since they work while Windows is running, you avoid a lot of the hardware-specific driver issues.

SQL Server in Virtual Machines?  Are You Crazy?

I know: I talk to you folks out there, and you hate the idea of virtual SQL Servers.  I feel your pain.  But before you throw this idea out the window, stop and think about the alternative.  Our options are:

• Keep the old physical dinosaur up, wait for it to die, and then run around in a panic
• Wait for budget money we’re never going to get
• Virtualize the server and have it perform better than the original hardware

Remember, even though it’s a virtual server and it may have some performance penalties, we’re still talking about virtualizing hardware that has already outlived its usefulness.  When you move a SQL Server from an old P3-based system and a few gigs of ram onto a new Xeon-based system and maybe crank up the ram a little, performance will go up - not down - as long as you’re not heavily oversubscribing your virtual server farm.  (That’s a separate discussion.)

Running virtual SQL Servers isn’t the best solution.  The best solution is for me to be driving a Porsche 911 Targa while my army of junior DBAs manage my crisp, newly installed SQL Server 2008 boxes.  But stop getting distracted from ideals that will never happen, stop trying to kill the dinosaurs with a cardboard sword, and do what you can with the tools you have.