Avoiding Common SQL Server Backup Mistakes – Follow Up Resources

In my recent SQL Server Pro Webcast (sponsored by Red Gate), I blurred through a number of details regarding how to Avoid 5 Common SQL Server Backup Mistakes. The event is/was free, and will be online for on-demand viewing for roughly 3 months after the date given. (Though it typically takes a day or so for it to become available for on-demand viewing after the live presentation date.)

Accordingly, I wanted to provide some additional, follow-up, resources to provide some additional context on many of the things that I addressed.

SQL Server Backup APIs

Early on in my presentation, I mentioned that it’s essential that third party backup solutions (or, more specifically: ‘plugins’ for SQL Server that are provided as ‘adapters’ for backup solutions that are typical NON SQL Server focused – but which want to provide some additional features and options) directly leverage the APIs provided by Microsoft for handling SQL Server backups. And again, this ISN’T an issue with all of the big/major 3rd party SQL Server backup solutions out there. Instead, it’s a concern with OLDER non-SQL Server backup solutions that also offer ‘plugins’ that ‘fake’ SQL Server backups without using the VDI and VSS APIs that are outlined in a bit more detail here. And the reason I keep stressing the ‘plugin’ angle here, is that the use of 3rd party SQL Server backups is typically a BEST PRACTICE in most environments.

RPOs and RTOs

It’s impossible to stress the importance of Recovery Point Objectives and Recovery Time Objectives. If you don’t have them, you haven’t communicated to management what they should expect in the case of a disaster. Consequently, even if you pull off the most spectacular and herculean recovery known to man following a disaster, you’ll still end up having to deal with cranky end-users AND management if you haven’t communicated RPOs and RTOs.

In fact, RPOs and RTOs are such a big deal that I decided to make them the focus of my very first blog post on my Practical SQL Server blog – here with SQL Server Pro. So take a few seconds to review SQL Server Recovery Time Objectives and Recovery Point Objectives if you’re not 100% comfortable with your own RPOs and RTOs and their communication to management.

Redundancy

Redundancy is something that’s pretty hard to cover in any detail within just a few minutes. Therefore, the best take-away I can offer when it comes to ‘redundancy’ is to use whatever techniques and options are at your disposal to give yourself as MANY options and ‘fall-backs’ as possible when it comes to having available backups to use in the case of a disaster.
To that end, I’ve talked previously about the notion of using ‘tiered storage’ to manage backups as both a means of achieving better performance AND redundancy in an article for SQL Server Pro called Maximize Storage Performance, and I’ve also blogged a bit about redundancy in this blog as well.

Security

Quite simply, addressing security (in the form of certificates, keys, and encryption – when juxtaposed against backups in terms of storage space and compression and so on), ends up being one of the more difficult things that SQL Server DBAs need to deal with in larger organizations – as clearly called out by this editorial by Steve Jones.
Case in point? I mentioned Transparent Data Encryption in my presentation, and I also mentioned compression. But the two simply don’t play well together. So, long story short, make sure that if security is IMPORTANT, that you’re regularly testing recovery processes and operations. Which, in turn, was the BIGGEST and MOST important thing that I tried to cover in my presentation.

Photo Credits

Finally, I used a couple of great photos in my slides – and wanted to provide attribution to those photos (or, at least, source information for where I found those photos):
Common Backup Mistakes Slide
Using the Wrong Tools Slide
Redundancy Slide
Security Slide