September 9, 2022 at 3:23 pm
What are best practices for setting up long term retention for an Azure SQL DB? Are there any common strategies to keep the costs low? How should one go about implementing it for keeping 10 year backups.
When I select weekly snapshots for 10 years, the cost is going through the roof (3.5x the cost of the DB just for LTR) and I realized there must be a better way to store DB snapshots, either with LTR or custom solutions.
I am no DBA and I am finding it hard to find any best practices and how it should be setup and suggestions or resources is appreciated. How would it have been done on an OnPrem SQL server.
September 9, 2022 at 6:57 pm
I guess you need to ask yourself, how likely is it that you're going to go back to Week 17 of Year 6 in your 10 years of weekly backups? How likely is it the year after? And the year after that? I'd be willing to bet, for most of us, most of the time, that is simply never going to happen. So, why then would you pay for that?
The best thing you can do for yourself when it comes to setting up backups and a retention strategy, is to stop thinking about backups and retention. Instead, think only about restores. What will you need to restore and when? What must you legally support for restores? That then drives the rest of the decisions, not the other way around.
Most of the time, when I've done restores, they were for the last few moments or maybe the last few days. Occasionally we went back a few weeks. Yes, we kept an annual (and I mean once a year) backup for 10 years, but nothing like you're describing. With something like that, the costs are going to extremely low.
However, you've got to determine and understand why you'd need that kind of level of backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2022 at 7:02 pm
Also, Azure SQL Database is backed up for you. Further, there is no capability for you to do a traditional backup. Instead, you can create what's called a bacpac, an export of the scripts that define the database and the data within them. It's very different than what would be done on premises.
This book is old, but the concepts are good. And it's free. That's how you backup on-premises. Here's an article I wrote for how to approach understanding the backup needs. If you click on my name there, you'll see a bunch of others.
Mostly, Azure has you covered. There is, beleive it or not, nothing you really need to do. They cover point in time restores, all automatically. Instead of sweating the backups, I'd go learn how to do a restore in Azure and then practice that. That's what really matters at the end of the day, getting the restore done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2022 at 7:57 pm
Also, Azure SQL Database is backed up for you. Further, there is no capability for you to do a traditional backup. Instead, you can create what's called a bacpac, an export of the scripts that define the database and the data within them. It's very different than what would be done on premises.
This book is old, but the concepts are good. And it's free. That's how you backup on-premises. Here's an article I wrote for how to approach understanding the backup needs. If you click on my name there, you'll see a bunch of others.
Mostly, Azure has you covered. There is, beleive it or not, nothing you really need to do. They cover point in time restores, all automatically. Instead of sweating the backups, I'd go learn how to do a restore in Azure and then practice that. That's what really matters at the end of the day, getting the restore done.
On that note, what is the RPO and RTO for Azure restores, Grant? And, with the understanding that I'm not an Azure user, can a single database be restored or does the whole instance need to be restored (I'm not even sure that the term "instance" is appropriate but, hopefully, you understand what I mean in Azure terms)?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2022 at 8:31 pm
Grant Fritchey wrote:Also, Azure SQL Database is backed up for you. Further, there is no capability for you to do a traditional backup. Instead, you can create what's called a bacpac, an export of the scripts that define the database and the data within them. It's very different than what would be done on premises.
This book is old, but the concepts are good. And it's free. That's how you backup on-premises. Here's an article I wrote for how to approach understanding the backup needs. If you click on my name there, you'll see a bunch of others.
Mostly, Azure has you covered. There is, beleive it or not, nothing you really need to do. They cover point in time restores, all automatically. Instead of sweating the backups, I'd go learn how to do a restore in Azure and then practice that. That's what really matters at the end of the day, getting the restore done.
On that note, what is the RPO and RTO for Azure restores, Grant? And, with the understanding that I'm not an Azure user, can a single database be restored or does the whole instance need to be restored (I'm not even sure that the term "instance" is appropriate but, hopefully, you understand what I mean in Azure terms)?
Hey Jeff!
I went and looked it up to be sure (these things change as you well know). RPO (recovery point objective for those who don't know, basically, how much data can be lost) for a point in time restore is 10 minutes. They do weekly full backups, and then 12-24 differentials all week long. RTO (recovery time object, how long to get back online) completely depends on the money you're spending. Higher service tiers are faster. They warn, very clearly, it could be up to 12 hours, depending on the size of your database and the service tier. All of it here at the link.
My experience, the 10 minute RPO seems very accurate based on tests. The RTO, well, I've never had to restore a truly big database. The little ones have all come back in minutes. Slower than on-premises, but to be expected. For the majority of people, heck, it's better than they can do themselves on-premises. Obviously, not you. Ha!
You can't really supplement the RTO other than adding additional services (geo-replication, stuff like that). The bacpac process, your only way to do a backup on your own, only really works if you do it off a database without activity. This is because it's just an export. If there's active transactions, you may export bad data. So, the trick here is, create a database copy (seconds to minutes, depending on size & service tier), then run the bacpac on that, nice and safe. So that means it won't supplement the RPO either.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2022 at 3:11 pm
This was removed by the editor as SPAM
October 5, 2022 at 5:07 pm
Awesome link and explanation, Grant. Thank you.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply