Feb 242012

This week I was very fortunate to speak at the February 2012 MADPASS user group meeting. I’ve seen lots of positive tweets about the group over the course of the past year and had been hoping to attend a meeting of theirs, so when I got the email a few months ago asking if I would present I jumped at the chance.

I left Chicago around noon figuring I would give myself plenty of time to drive there, find my way if I got lost, and/or sit in traffic. I didn’t end up getting lost or hitting any traffic, so I ended up with a few hours to kill. Fortunately the building MADPASS meets in has this awesome room called “The Pub” which very much looks the part. I think my building needs one of these! I spent a few hours in there, got some work done via wi-fi and made sure my slide deck was in order.

The Pub

The Pub

MADPASS meetings aren’t held in the pub, though I understand the first one was because the pool table wasn’t there yet. Once the pool table was installed the meetings moved next door to a meeting room featuring cushy chairs and a fireplace. This is much different from any other presentation space I’ve seen since it doesn’t have a lectern or hard-wired projector, however the folks at MADPASS have a cart full of electronics with all the equipment necessary to present, webcast and record the meeting.

After the welcome and introductions, Jes Borland started off with a 30 minute version of her “Making Your Voice Heard” presentation on the benefits of raising your profile in the SQL Server community through twitter, forums, and blogs, and some tips and tricks to help accomplish that goal.

After Jes came food and networking. Dinner consisted of pizza, soft drinks, beer (we need this at the Chicago meetings!!) and birthday cake. February marks MADPASS’ first birthday, and of course no birthday would be fun without some cake. Jes has a photo of it in her blog post about the meeting.

I left dinner a little early to make sure all my equipment was setup to present, and it’s a good thing I did that because I ended up needing every second of that time and then some. Apparently I’m the first person they’ve had that’s given their presentation from a Mac and LiveMeeting didn’t want to play very nicely. I ended up getting started about 20 minutes later than planned and with screenshots recorded via a join.me session. I ended up running a bit long and rushing through the last few slides because I started late, but managed to get everything in. I hope everyone enjoyed it and learned a trick or two. I also got some really great questions afterwards. If you’re looking for the demo scripts I used, you can find the latest version of them along with other resource material here.

I’d like to thank the MADPASS board: Tim Benninghoff (blog), Jes Borland (blog | @grrl_geek), Matt Cherwin (@control_group), Steve Novoselac (blog | @scaleovenstove), and Tony Sebion (@tonysebion) for the opportunity to speak, and also Confio for sponsoring the meeting and providing swag and raffle prizes.

Feb 162012

MADPASS LogoNext Wednesday I’ll have the pleasure of speaking at MADPASS! I’ve been meaning to visit this group for a while, so I’m very excited to head up there and be speaking. Here’s all the pertinent info:

WHERE: 2310 Crossroads Drive, Madison, WI 53718

WHEN: Wednesday, February 22, 2012. 5:30 PM

I’ll be giving one of my oldies-but-goodies, a presentation entitled “Backups and Restores In Depth”. I’ll discuss some of the finer points of backups, restores, and how they work. I’ll also cover the three recovery models in detail and add some best practices along the way.

In addition, Jes Borland (blog | @grrl_geek) will be giving a talk on communication, presenting, and social networking entitled “Make Your Voice Heard”.

Hope to see you there!

Feb 142012

T-SQL TuesdayHappy T-SQL Tuesday to all! (and happy Valentine’s Day too!) This month’s T-SQL Tuesday comes to us courtesy of Steve Jones (blog | @way0utwest). Steve is asking us to write about “big data” – specifically the problems we’ve solved or interesting ways we’ve found to work with it. In honor of the holiday, I’m going to take a slight tangent from that and talk about why I love working with it instead.

First, let’s tackle the term. “Big data” is interesting in that I’ve yet to see a universally-accepted definition for it. I’ve heard lots of different numbers from lots of different people. To me, a database in the 5-10 terabyte range and larger qualifies as a very large database (VLDB) and thus, big data.

Working with big data presents some very interesting challenges that will allow your skills as a DBA to shine. Whether they shine like gold or like NASA’s super-black material will depend on your skillset. VLDBs have a habit of exposing poorly performing queries very effectively due to their size. In smaller databases, queries performing slowly (perhaps due to lack of proper indexing) might be tolerated in some situations if a query completes in a few minutes. When you’re dealing with big data, however, table scans might take hours, making issues such as indexing much more critical to acceptable performance. DBA tasks such as backups and consistency checks will also take much longer with large amounts of data, meaning new techniques might be necessary for said tasks to complete in a reasonable amount of time. SQL Server native backups might not be possible due to performance or disk space, thus other methods such as SAN Snapshotting may be required. Integrity checks may take large amounts of time to run or be impossible due to the load they place on a production server, meaning they have to be run on another machine from a restored backup. This is by no means an exhaustive list – there are tons more ways that increasingly large quantities of data can cause issues.

Big DataThe issues above along with many others crave solutions, and another reason I love big data is that it’s brought about a lot of innovation to the information technology field over the past few years. Non-relational databases have existed for a while, but they have recently grown in popularity and number of offerings due to the increasing need for handling big data. As reliable and popular as relational databases are, the need for databases that give up some relational guarantees in exchange for better handling of extremely large datasets gave birth to the NoSQL movement, which will undoubtedly continue to reshape the future of the web and handling of extremely large datasets. Some may worry that NoSQL is going to replace their beloved relational databases, but I don’t share that belief. Both database models address different issues and should be able to coexist as they already have for years.

To sum it all up, I love a challenge, and since big data has plenty to offer in that department, I love big data too.

Feb 092012

If you’re reading this, you probably already know that while backup strategies are important, restore strategies are what really matter. If you aren’t able to restore a database when a disaster has occurred and money’s on the line, you may find yourself very quickly updating your resume and leaving town. Practicing restores will not only tell you that your backups work, it also gives you good practice for your restore process. (If you don’t have a restore process, then it’s an excellent opportunity to create one!) When the CEO is tapping you on the shoulder wondering when the database will be back online, wouldn’t it be great to look like a pro flying through the menus (or better yet to have everything scripted out) instead of clicking around like you haven’t done a restore in years? You’ll also have a pretty good idea of how long a restore will take if you’ve practiced recently and kept track of the time, which will come in handy when everyone wants to know how long it will take to get things back up and running.

About a year and a half ago, I took the plunge and bought a Mac. I’ve been very happy with my purchase. A few months after buying it I decided to drink a little more of the Kool-Aid and start taking backups using Time Machine. I saw this as a way to preserve my system state in case something should ever go awry. A little later when OSX Lion was released, one of the new features it included was FileVault 2, a tool which can encrypt an entire hard disk. I tried FileVault 2 a few months ago just to see what it was like, and kept it enabled when I saw no significant performance hits for my everyday tasks.

RestoringFast forward to last week. I was interested in seeing if my backups actually worked or not, so I decided to restore my entire computer to my most recent Time Machine backup. According to everything I had read, restores would be a snap. Boot the computer while holding down Command-R and you’ll be taken to Lion Recovery, which presents you with a few options including running the disk utility to repair hard drive errors and restoring your computer from a Time Machine backup. After selecting restore, you choose the source drive containing the backups, the backup you’re restoring, and the destination drive you’re writing to. This is where things got interesting for me, because my hard drive never appeared in the list of destination disks. I waited a few minutes to be absolutely sure nothing would pop up, and then started looking for other options.

I had recently read an article on Ars Technica talking about issues with encrypted drives in Lion, and while the author of that post didn’t have the exact same issue that I did, he did find a solution that helped me a little. If I opened the disk utility I could unlock my encrypted disk with my password, and then when I went back to the restore menu my drive had magically appeared in the list of possible destinations. I selected my drive and started the restore process. Mission accomplished, right? After a while I got the screen saying the restore had completed successfully and a reboot was necessary. Rebooted, and I was taken right back to the recovery manager. I tried disconnecting my Time Machine disk and rebooting again, and was greeted by a wonderful picture of a folder with a question mark on it. I figured this means my machine couldn’t locate a bootable disk. I rebooted again and used Command-R to get back into Lion Recovery. Thinking maybe my most recent backup was bad, I tried restoring a different backup and got the same result. After a few more tries with no luck, I decided to give in and call Apple support. I’m not a very big fan of calling tech support, but I did pay for AppleCare so I might as well get my money’s worth.

I got through to a technician right away (possibly because I was calling during the Super Bowl) and explained my situation and everything I had tried, including the fact that my drive was encrypted. He had me run disk repairs on my laptop hard drive and the external drive containing the backups, no errors were found on either. He told me he had no documentation mentioning what to do for a drive that’s been encrypted with FileVault 2, but passed me along to a higher level of support as they were more equipped to handle such an issue. The next technician walked me through deleting and re-creating a new encrypted partition on my drive, and then sent me on my way to do another restore. She gave me her contact info and said I was more than welcome to call back if I had further issues, but if the restore didn’t succeed at this point, there was probably something corrupt in my backups.

This restore completed successfully just like the others, but my machine still wouldn’t boot. Out of frustration I decided to create a non-encrypted partition and try restoring to that. Lo and behold this worked and the machine booted right back to the way it was a few hours earlier. Due to the issues I encountered it took way longer than I had hoped, but at least I didn’t have a pretty aluminium paperweight anymore!

Based on my experiences, it appears that a Time Machine backup can’t be restored to a drive encrypted with FileVault 2. It also appears that Apple support is not aware of this, or at least it was never brought to my attention that there might be an issue. I’m happy I was able to figure this out, and the whole ordeal made for some great practice. Furthermore, if I ever need to restore in a hurry, I’ll now know what to do.

Have you practiced your restores lately?