1

One of the clients at my workplace asked me to find out information on what would be the best possible or recommended approach to run maintenance jobs such as backup job for a SQL Server 2016 hosted in Azure.

Should I be running native SQL backups and dumping the backup files in Azure blob storage, or do I need to ask the client to have a file share as a placeholder for the backup or is a locally attached disk better?

0

I'd advocate for you doing native, compressed, backups to Azure blob storage and using SQL Server managed backup. But the business may require you to store them locally.

Just remember that you pay for egress, so copying those backup files to your local DC may cost you more than if you just store them in Azure directly.

HTH

  • I did not understand what you meant by doing native, compressed backups to Azure blob storage and using SQL Server managed backup, so basically am I going to use custom scripts to perform native SQL backup stored locally or use SQL Server managed backup through Azure and dump it into the Azure blob storage. I am a little confused as I don't much about Azure etc. – Feivel Mar 8 '17 at 22:30
0

My approach to backups is the same, irrespective of where the SQL Server instance sits.

  1. SQL Server native backups to a network share, or "local" drive if you're on an Azure VM (I tend to avoid writing directly to Blob Storage for backups, because it's slow).

  2. A process that copies these backups to "offsite" storage. In most cases these days, this is Azure Cool Blob Storage, with some form of Geo-Redundancy. My process might be AzCopy, or PowerShell, a batch file, or my own Database Backup Sync tool. It doesn't matter how you do it, as long as you're doing it. The point is to get a copy of the backup files as far away from the source as soon after the backup as possible.


Don't think of it as a backup, think of it as the fastest (RTO) way to recover the database to a consistent state, at a point in time (RPO) that satisfies the business requirements. Stated more plainly:

  • how quickly do you need to restore in a disaster recovery scenario?
  • how much data is the business prepared to lose?

In other words, focus less on where the files are going, and more on the reliability of the backup files, and your restoration process, if everything catches on fire.

That means having checksums enabled on backups, a regular test of restoring the databases (on another machine somewhere), followed by a DBCC CHECKDB to ensure consistency in the database.

The elegance of Blob Storage as a secondary store for backups is that you can pull the files down from any Internet-connected machine and test the restore process.


I do not discuss typical maintenance tasks you should also be running on your instance, which includes DBCC CHECKDB, index and statistic maintenance, etc. Those are outside of the scope of this answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.