Must-Have Resources - SQL Server Backup & Recovery

KKline — Thu, 08 Dec 2011 20:36:00 GMT

One of the things that drives me crazy as I'm getting older is that my brain is losing the capacity to differentiate version numbers. As I speak travel around speaking with customers and at conferences, I find my self saying things like "I can't recall if this problem was fixed in SQL Server 2000 or 2005. But you don't have to worry about that any more." Or things like "That feature was added in SQL Server 2008 R2, eh, or was that version 6.5. DOH!" followed by a loud slapping sound as I whap my palm into my forehead. The Internet doesn't forget either. Recommendations that were once helpful, if not outright essential, now are neutral or even downright bad. So now, whenever I put together new presentations, I always spend a lot of time in research, reassessing my knowledge on the topic. (That doesn't mean that I'll extemporaneously say it wrong, because I speak in an off-the-cuff and rapid style. But at least my notes are usually correct).

Case In Point

Take backup and recovery (B&R), for example. I've been writing about and performing B&R for years. I'm even part of the team that builds the most popular B&R tool in the SQL Server space. It's an extremely important part of what I do. And yet, even after spending a couple days re-researching topics (yet again), I still manage to get a few of the more specific details wrong because of changes over the years in the feature set. How so? During an Expert's Perspective webcast last week (and available on-demand) covering the top backup and recovery mistakes on Microsoft SQL Server, I mis-explained the details concerning how SQL Server performs a differential backup. A very clever SC on our team who was also attending the webcast pointed out to me after the session that I'd described differentials working at an 8k page-level when, in fact, they work at the extend-level (that's a block of eight 8k pages). #FACEPALM! When describing the differential backup I said that a bit was flipped on each page header and SQL Server would only back up those pages. Instead, whenever a page is changed a bit is recorded on the differential change map (1bit per extent), the backup process then queries these map pages and backs up those extents that have been marked as changed.

Watch the on-demand video of Performance Tuning SQL Server for Backups and Restores, with me, Iain Kick, and Brent Ozar (blog | twitter). Free, but registration required.
Watch the on-demand video of Ten Incredibly Painful SQL Server Backup and Recovery Mistakes, with me and David Gugick.

The Research Reveals

The cool revelation here is not that I can admit my mistakes. The take-away from this blog post are all the great articles I read writing my latest slide deck. These blogs and articles were so good that I needed to spread the word. Add these to your must read list:

Virtual Log Files must be tamed!

Performance impact: a large number of virtual log files Part I and Part II by one of my favorite not-so-prolific bloggers, Linchi Shea (blog). The thing I love about Linchi's content is its rich benchmarking information. Few SQL Server experts out there routinely test the behavior and performance of specific SQL Server features like Linchi does.
Slow recovery times and slow performance due to large numbers of Virtual Log Files, by Graham Kent (blog) of Microsoft Sweden, shows that VLFs impact not only the processing time of transactions but also B&R processes.
Lots of VLFs are Bad, by Tony Rogerson (blog | twitter), was the article that first got me interested in this behavior.

Backup and Recovery Myth Busting

Breaking the Backup Chain - Redux (Or 'Eating Crow'), by Michael K. Campbell (blog), shows that out-of-band database backups break differentials not transaction log backups.
Backup with COPY_ONLY, How to Avoid Breaking the Backup Chain, by the eminent Paul Randal (blog | twitter), uses clear writing and first-class demos to illustrate the principle of breaking backup chains in Microsoft SQL Server. Other great blogs of a related nature from Paul include:

Other Good Sources

And just in case you need a refreshing in the basics of SQL Server backup and recovery, check these out:

Information Hoarder No More!

KKline — Wed, 02 Nov 2011 14:16:00 GMT

I hate to admit it, but I'm a hoarder. Yes, like those insane people on the A&E TV show. Only my hoarding is all virtual. For example, take the image above. That's just a tiny part of my Google Reader home page. That's a tiny part of my Google Reader home page AFTER reading most of the day on a recent Sunday. I still had thousands of entries to go after hours of reading. On top of that, I subscribe to some technical e-newsletters. I'm a member of quite a few LinkedIn discussion groups, each of which produce daily and weekly newsletters. (I only subscribe to the weekly versions). Then there's Twitter too. In all, there are THOUSANDS of interesting floating through the ether which I'd been trying to collect and, occassionally, read. Bah humbug!

The Downside of an Information Hoard

You might think that, as an IT professional, hoarding information is good for you. After all, the more you know, the more effective you are at your job, right? Uh - no. I've discovered a few downsides to keeping all of this information around. First, I lose time on administrivia, uh, I mean administration. I can't think of a time when a search on Google has not produced the information that I'm interested in. So by spending time keeping up with my blog feeds, adding new blog feeds, deleting old ones, and so on, I lose time administrating something that really doesn't need to be administrated.

Second, there's the time it takes to read all of these entries - many long hours to read thousands of entries per month. I've actually figured out a little trick to make this go a lot faster. What's that trick? Well, previously, I used to start reading my entries and then clicking "Next Entry" each time. Even when I skipped a lot of entries, just slogging through them all was a big time drain. Now, I simply select large swathes of entries that I'm not interested in and click "Mark as Read" without ever opening them. Works great! I've also gotten a lot more aggressive about dropping bloggers and RSS feeds that offer low value. A lot of bloggers have popped up who only recount things which are available in Books On-Line. Why spend any time on that at all? I'm looking for strong insight, experiences, and analysis - not simple technology facts. (A follow on thought to this tip is that "Bloggers Should Write Meaningful Article Titles!")

Third, and more importantly, information hoarding takes a big emotional toll on me. Maybe it's a factor of just how my brain works and is completely inapplicable to you. But in my case, I always carry a subtle nagging feeling when I have unread entries in my various accounts. Even when I know that these entries are optional and that it's not necessary for me to read any of these things, I still feel like I should read them. Basically, it makes me feel like I should be working all the dang time and that makes me feel anxious. Anxiety makes me less productive and more prone to burn-out. And anxiety bleeds through my work hours into my home life. It makes it hard to enjoy a movie with the kids or some gardening in the yard. All because there's that feeling that I haven't gotten the hoard processed yet.

The Solution to the Information Hoard

I mentioned a couple specific techniques for thinning out the hoard in paragraph two. To summarize, first, subscribe to only those bloggers, feeds, and newsletters which add actual understanding to your life. Second, open and read only those entries that matter and skip the rest. Those are techniques for dealing with lots of entries to read. But my last problem, the anxiety issue, was a little bit harder to solve.

Like a lot of internal ways of handling life's problem, the answer is simple but not easy. Similar life problems with simple but difficult solutions might include feelings of guilt (the solution is confession) or anger (the solution is forgiveness, either of yourself or for the other party). So what's the solution to anxiety? Here's my thought process - the answer to my information hoard is about values. What do I mean? I value these various things because they make me better at my job. Losing things of value causes me some anxiety. My anxiety has its roots in the feeling that I'm letting things of values (these various blog entries) slip through my hands. Why would you ignore things of value, or even worse, get rid of them?!? On the other hand, if you asked me what I really and truly valued most in this world, I'd answer with "my family and loved ones". But again, do I actually demonstrate this priority with my time? Not nearly enough. Therefore, the answer is to properly appraise the value of my time. When I think about it rationally, I think that this sort of reading is worth about 30 minutes per day, maybe a full 60 minutes when there's something really important to learn or someone really significant to listen to.

That's it. After 30 minutes, I can walk away from any sort of reading guilt-free. So what's my new solution to the information hoard and the anxieties that it's been producing? A quick check on the writers and topics I care about the most and then "MARK ALL AS READ". Yes, there will be more to read tomorrow. But now I no longer carry an ever increasing load saying to myself "Someday I'll get to that". I feel better already.

Feedback Requested

What's your strategy to dealing with your information hoard? Are you still keeping everything you ever produced or read digitally? Does my approach sound reasonable and workable to you? Or am I off base?

Thanks!

-Kev

-Follow me on Twitter

Search results matching tags 'Tips' and 'Productivity'