Data Safety Is No Accident

(With apologies to David Foster Wallace.)
I follow various tags on stackexchange sites, primarily database-related things. If you do this for long enough, you will see certain questions come up again and again. Frequently Asked Questions. FAQs. This is not a new phenomenon, it dates from the days of USENET and probably even before then
One of those FAQs is: "OMG, something horrible happened to my database, I don't have any backups and now I think my boss will yell at me and hurt my feelings. How can I restore the data before that happens?".
As a DBA, your first job is being able to restore the data. Just backing up the database isn't any good if you can't restore it. It doesn't matter if you are using SQL Server, Oracle, MySQL, postgresql or any other data storage technology. It doesn't matter if you are an "accidental DBA" and would rather be coding. (I started my career as a coder and, frankly, I would rather be coding too.) People rely on you to protect their data in ways that they don't understand and can't do for themselves.

Backups should be boring. Restores should only be slightly more exciting, since they should be rarer (much rarer) than backups. If your backups and restores are not boring, you are doing them wrong.

Before you run upgrade or update scripts from the development team, protect yourself. Stuff happens even when people are doing their level best. A last minute change might have deleted a crucial term in the WHERE clause of that update statement. The script that you recieve might be a concatenation of several other scripts, and the whole Magoo has never been tested at once. Some snafu with the VCS means that you are given the wrong version of the file.

Ask yourself, "If something goes bad with this script, what will I do to reverse it's effects?" As a DBA, can easily protect yourself by taking an extra backup (remember to use Copy-Only backup commands if you are using a differential backup strategy) or by taking a database snapshot.

For scripts that run on large databases that only change a procedure or view, it might not be worth the time to back up the whole database. I like to script out the body of that object. I was doing that so often, I wrote a little PowerShell utility so I could do it quickly. Sure, that procedure is probably in the VCS, but sometimes finding the right version in a hurry isn't so easy.

If you don't have DBA rights, you can protect yourself by adding some transactional protection to the script that you have been handed (be sensible - you don't want excessive log file growth). If you want to make a backup copy of the table you are modifying, that is OK but don't let it hang around forever. Add an item to your calendar that will force you to come back and drop that backup copy. Or consider BCP-ing the data out. It's quick, once you get the hang of the bcp command syntax.

If you are inserting rows and find a problem, you might be able to delete the new rows if you can describe them with a WHERE clause. Many tables have some sort of createdby or updatedby columns. BUT, watch out for triggers, as they can modify data in other tables in surprising ways and, when it comes to databases, surprises are almost always bad.

Moving a Windows XP install from VPC/VHD to VMWare ESXi 5.0

I had an old Windows XP VHD that I used as a development environment for a client. I don't work with that VHD anymore, but I didn't want to just delete it. I did want to get the VHD off of my laptop to free up storage space and cut down on backup requirements. It seemed that the thing to do was to put in on my ESXi 5.0 host. But, how does one do that? Ah, a learning opportunity...

One googles, and one finds that VMWare provides a tool called the "VMware vCenter Converter Standalone Client". This tool is aimed at industrial-scale conversions and it can handle a couple of different situations (different sources, primarily). I downloaded the tool, installed it, and used it. It was surprisingly easy.

I did get some warnings about not having some sysprep files in place. That was a little scary, but I had a fallback path (the old VHD). If the new VMWare VM was broken, I would only have lost some time and gained some experience. I ignored the error. (From googling around, those files can be obtained from Microsoft and that might be worth doing, if you are converting a large number of machines.)

The tool converted the VHD VM to a ESXi VM and placed on my ESXi host. Using the VSphere management console, I started the new VM and had a look. I expected that I would have some slogging to go through, maybe I'd have to download drivers for the VMWare-specific "hardware". 

Nope. 

 

While the VM was booting, I was distracted by another task. When I got back to working on the VM a few minutes later, I found that it was installing 59 Windows updates and was humming along. When it finished installing the updates, it rebooted. I could then log in using my old credentials. Windows complained about having been moved to "new hardware", but it allowed me to re-enable the license with just a few mouse clicks. No phone calls, no drama. I then updated the virus software to Microsoft System Essentials from the older Microsoft product.

 

I expanded the desktop to 1600x1200, to match my venerable Dell FP2001 panel. (This Dell is the best monitor I have ever owned. Between 1990 and 2001, I owned CRTs from Princeton, MAG and Sony. They all died. I even had the MAG fixed once and it died again. I have been using my Dell for 12 years. I've never had a problem with it.)

I configured the VM to use one vcpu. It is pretty snappy, maybe a touch more sluggish than running on my three year old Lenovo T500. wprime says that the VM is only a little slower than running natively on the Core2 Duo in my laptop.

 

So, the whole process went smoothly and I could see using the tool to do mass-P2V-ing of systems.