The Costs of Azure SQL Database Backups
Updated April 26th 2016
This post is dated, and I decided to update it with information from Azure SQL Database: Built-in Backups vs Import/Export and from SQL Database FAQ.
Azure SQL Database billing is prorated daily hourly. This means that if you create a database and delete it right away, you are be billed for the full day hour. Each hour is billed at the highest service tier + performance level that was applied during that hour.
I bring this up because the Azure SQL Database automatic backup feature is was built on top of the import/export service. The export follows best practices in order to create a consistent point-in-time copy of your database. This copy is treated as a second database and is billed as such. Then the service exports this copy it to a bacpac file and places it in your Azure Storage.
Today the built-in backup feature is included in the hourly cost of each database. The following is a quick summary of its capabilities.
What are Azure SQL Database Built-in Backups?
Basic, standard, and premium databases are backed up automatically in Azure SQL Database. These backups are retained for 7 days, 14 days, and 35 days respectively. With these backups Azure SQL Database supports two restore capabilities.
- Point in Time Restore allows a database to be restored to any point in time, up to the millisecond, within a database’s retention period.
- Geo-Restore utilizes a database’s latest geo-redundant backup to recover a database. This means a database can be recovered in the event of a regional outage with the 1 hour old geo-redundant backup.
Capability | Basic tier | Standard tier | Premium tier |
Point In Time Restore | Any restore point within 7 days | Any restore point within 14 days | Any restore point within 35 days |
Geo-Restore | ERT < 12h, RPO < 1h | ERT < 12h, RPO < 1h | ERT < 12h, RPO < 1h |
Standard Geo-Replication | not included | ERT < 30s, RPO < 5s | ERT < 30s, RPO < 5s |
Active Geo-Replication | not included | not included | ERT < 30s, RPO < 5s |
Why are backups important?
In Azure SQL Database, the 3 copies of the database are identical. If a careless user decides to execute a DELETE statement or an UPDATE statement without a WHERE clause, the data gets altered in all 3 copies!
Incidentally, the 3 copies are there to deal with infrastructure failures not users. The automatic backup feature available for Azure SQL Database is essential, because if something goes totally wrong, you will have a backup to work with.
The table below is a quick comparison of the Import/Export and the built-in backup and restore capabilities.
Designed for Disaster Recovery |
Support PITR |
No operational overhead |
Transactional consistent backups |
No additional cost |
Restore to On-Premises |
|
DB Export |
No |
No |
Export needs to be externally triggered |
Required DB copy before backup. |
Storage and extra DB cost. |
Yes |
Built-in Backup |
Yes |
Yes |
Yes |
Yes |
Yes |
No |
Don’t forget blob storage costs too.
I agree that it seems expensive (but essential). Most users will want to back up at least daily which means doubling the cost of the database. This does seems a tad excessive for a backup operation which may only take minutes. It is a shame that you cannot backup more often than daily as well. Given the fair amount of negative comments I see about this feature (mainly costs), hopefully MS will improve it before it comes out of preview.
LikeLike
The solution to the cost issue is pretty straight forward. Simply change the backup so it doesn’t copy the database first, just take the bacpac from the database. This does have the downside that the backup is not necessarily transactionally consistant, but it’s a trade off I’m sure some people could live with (I certainly can)
LikeLike
Billing granularity appears to have moved from daily to hourly. I think these are wall-clock hours, so schedule your backups at the start of the hour. https://azure.microsoft.com/en-us/documentation/articles/sql-database-faq/#what-if-a-single-database-is-active-for-less-than-an-hour-or-uses-a-higher-service-tier-for-less-than-an-hour
LikeLike