Making the Case for Native SQL Backups

It’s 8AM…

I’ve configured alerts for missing SQL Server database backups. When I review my emails first thing in the morning I can relax knowing each database has been backed-up the night before. How do I know? Because I didn’t get a missed-backup alert email! Ok, fair enough, but is that good enough?

What’s really going on in the wee maintenance-window hours of the morning so that my inbox is clear of missed backup alerts? Who or what is taking those SQL Server database backups? How, exactly, are they being taken? Where are they going? And why?

Three flavors

Let’s begin by zooming way out and looking at three flavors of database backup solutions:

1. Native SQL Server Database Backups – part of the SQL Server product, initiated via a TSQL BACKUP statement. You have a lot of options here. Backups can go to a local drive, or to a remote share, or both, backups can be encrypted, striped, compressed, copy-only, scheduled via SQL Agent, and so on…
2. Third-party SQL Server Database Backups – any of a number of services or utilities with a ‘hook’ into SQL Server that facilitates scheduling database backups to remote centralized storage. A few common tools include NetBackup, Red-Gate, Idera, Veritas, etc. (for more google SQL backup tools).
3. OS-level File Snapshots – any number of services, applications, or tools that grab an image of the entire system and/or the files on a system and store them on remote storage. A handful of examples include Windows or Azure Server Backup, Avamar, Cabonite, etc. (for more google OS backup tools).

 
Each of these different solutions have their strengths and each of these could be viable for backing up SQL Server data. But what about when we need to recover the data? That’s when things get complicated.

Alphabet Soup

We backup data in order to be able to recover it for these reasons:

• to refer to historical data – let’s call it BCE,
• to rebuild systems after infrastructure failures – DR of course,
• and to repair an unintended data modification – DOH!

 
A data restore plan is quantified by its Recovery Point Objective (RPO). RPO is the gap between the most up-to-date data recovered and the time of an incident, and its Recovery Time Objective (RTO) – the time it takes to restore the data. Practically speaking, RTO and RPO mostly apply to DOH situations. For example, it’s not typical that the RTO requirement is difficult for BCE restores – the restore can be planned out well ahead of time. Similarly, in a DR situation (or, thankfully, and more familiar to many of us, a test-DR situation) the RPO requirement is not critical. Oftentimes it is one day. On the other hand, when the call comes in to fix a DOH – for instance a table is accidently dropped or corruption is discovered – minimal RTO and RPO are key.

Take this line of thinking back to the backup flavors.

The case for Native SQL Backups

The way to minimize outages and loss for DOH situations is to keep the backups as near to the data as possible. The closer the backups are to the data, the simpler the restore plan, and the lower the RPO and RTO. That usually means native SQL backups to a local drive. (They should also be copied to a remote location but that is beside the point).

Third party SQL Server database backups make ease of management the priority. They may be fine for DR and BCE, but if it’s complicated or slow to recover data, RTO lags. OS level file backups are probably critical to the OS team but due to relatively high RPO and RTO values they are not ideal for BCE, DOH, or DR for SQL databases.

Getting back to the original questions, all of these options do what they’re supposed to do, namely backup the databases and keep alerts out of the inbox. But do they meet the business requirements for RPO and RTO?

I know there are a lot of generalizations here – each environment is different and any combination of these flavors might make good sense. But the take away is summed up by the old DBA truism: database backups are only as good as database restores (or something like that). No matter the solution, good database restores require good database restore plans. If I know those plans are thought-out, tested, and in place then I know the lack of missing backup alerts in my inbox really does indicate all is well and I can enjoy my morning coffee.