This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
Once you have configured your backups as per your company standards, you should also know about the storage consumption and it’s charges.
As you might already know from the previous blog that PITR is configurable and can be set from 1, 7, 14, 21, 28, 35.
What it means is you Azure has to maintain Full, Differential and Transaction Log backup for supporting the PITR option that you have set. Taking the example for 1 day for PITR. As you know any recovery starts with restoring the Full backup so for any day of the week even if the PITR is set for 1 day Azure has to keep the last Full backup.
The other thing to consider is all the backups are always compressed, irrespective of whether you have enabled TDE or not.
For the pricing of PITR backups, the only costing involved is the storage, Azure doesn’t charge for taking backups and maintaining them but only the storage involved in keeping them is billed. There is one exception when you have DTU model there is not additional cost of keeping the PITR backups as it is part of the database pricing.
For the vCore model, the storage amount equal to 100 percent of the maximum data storage size for the database is provided at no extra cost anything more it is billed as:
Total billable backup storage size = (size of full backups + size of differential backups + size of log backups) – maximum data storage
Long Term Backups
The backups are stored separately than the PITR backups, anyway you cannot see the backups stored in some storage account, to maintain the PITR backups Azure copies the relevant Full backups from the PITR backups and copies it to a different location.
For PITR, to maintain and provide the fastest possible recovery from the restore, Azure takes Full, Differential and Transaction Log backups but for LTR it doesn’t need to keep everything but just the Full backups.
If you change the LTR from some previous configuration, the backups maintained for the old settings are not deleted but maintained till its life cycle. The new setting is only applicable to the new backups.
These backups are always billed irrespective of the pricing model.
To check the pricing involved in maintaining the LTR backups I would suggest to check the pricing calculator.