Backup storage consumption on Managed Instance explained

[Danimir Ljepava]

Automated backups on Managed Instance

Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from accidental corruption or deletion. Azure SQL Managed Instance automatically creates the database backups that are kept for the duration of the configured retention period (1-35 days for active databases, and 0-35 days for deleted databases). With the PITR (Point In Time Restore) functionality, it is possible to restore your database at any given point in time within the retention period, and with one minute granularity, providing you with safe net from accidental data deletion or data corruption. To learn more about our backup technology, see Automated backups for SQL Managed Instance.

How much automated backups cost

Customers will get the equal amount of free backup storage space as the reserved data storage space purchased, regardless of the backup retention period set. If your backup storage consumption is within the allocated free backup storage space, automated backups on managed instance will have no additional cost for you, therefore will be free of charge. Exceeding the use of backup storage above the free space will result in costs of about $0.20 - $0.24 per GB/month in US regions, or see the pricing page for details for your region. 

Example: For example, if you provision Managed Instance with 4TB of reserved data storage, you will get 4TB of backup storage space for free, regardless of the database backup retention period set. The provided backup storage space is used for all databases and all backups cumulatively. Exceeding usage over the free space in this example will come at charge. Important to note is that Azure invoice will only show the excess backup storage used, not the entire backup storage consumption.

As a general guidance, customers with 1-7 days backup retention on their databases will most likely not exceed usage of the free backup space, whereas customers with 35 days backup retention period are very likely to exceed the usage of the free backup space. Please note that this is only for orientation purposes and not necessarily the case, as the excess backup storage consumption will depend on individual database size and workload usage patterns.

How backups pile up

SQL Managed Instance supports self-service for point-in-time restore (PITR) by automatically creating full backup, differential backups, and transaction log backups. Full database backups are created weekly, differential database backups are generally created every 12 hours, and transaction log backups are generally created every 5-10 minutes, with the frequency based on the compute size and amount of database activity.

The first full backup is scheduled immediately after a database is created. It usually completes within minutes, but it can take longer when the database is of a significant size. After the first full backup, all further backups are scheduled automatically and managed silently in the background. The exact timing of all database backups is determined by the SQL Database service as it balances the overall system workload. You cannot change or disable the backup jobs.

In order to support restore to any point in time within the retention period, we retain the entire "backup chains" (full + differentials + log backups) between each full backup until they are no longer needed. This means that we are likely storing multiple "backup chains", based on the configured retention period for the database.

Some of the ways you could cause backups to grow excessively

The list provided in this section on how you can exceed the free backup storage space and go into the billable excess storage is for orientation purposes only. Some of the ways which can influence going into excess backup storage utilization are:

• Having long backup retention periods than needed, for example 35 days for all of your databases on a single Managed Instance.
• Frequently adding and deleting large databases, while keeping a high backup retention period. For example, creating and deleting 1TB database daily, while keeping the backup retention period for 7 days, will quickly accumulate to tens of TB of excess backup storage in a month.
• Your application performs large write operations more frequently than needed - for example frequent index rebuilds.
• You are having large data load operations (especially analytical data mart \ DW workloads) without using non-clustered indexes only and loading rows of more than 1 million.
• Your application is heavily relying on using permanent tables for temporary results, instead of TempDB.
• You are using TDE encryption for non-sensitive data. This is because your backups are compressed by the system, and encrypted databases cannot be compressed as much as non-encrypted databases.

DISCLAIMER: Your actual backup storage consumption can depend on many factors, namely backup retention rate and apps/workload usage. The above examples are for orientation purposes only. 

How do we calculate excess backup storage

The billing meter for PITR (SQL Database Managed Instance PITR Backup Storage) is charged at a rate of price ($) per GB/month with an hour-granular billing. The basic unit of calculation is 1GB of excess backup storage space consumed per month. The cost stated on the pricing page means that in order to get charged the basic unit of 1GB per month (for example $0.20 per GB/month, as priced in the East US region), your had to have 1GB of excess backup storage space consumed for the entire duration of a single month. The "hour-granular billing" means that every hour we check the backup storage consumption and communicate it to the billing system. 

Since we charge price ($) per GB/month, the excess value has to be calculated from a monthly value to an hourly value.

Example

• At 10:00am today the backup storage usage for all databases on managed instance was 750GB.
• The reserved data storage (and free backup storage space), at 10:00am today, was 500GB. 

This means that at 10:00am today we would need to charge for 250GB of excess backup storage space (500GB of backup storage is free, as this equals to the same amount of reserved data storage space purchased for managed instance). As the numbers of days per month varies throughout the year, we use 31 days in a month for the calculation formula, as this is more advantageous to customers than would be a lower value of 30 or 28 days. Therefore, we calculate 250GB / 31 days / 24hr = 0.336GB to transform a monthly charge for excess backup storage consumption (in GB) to an hourly charge. The 0.336GB of excess backup storage space consumed for this hour is multiplied with the price stated on the pricing page (under backup storage section, point in time restore). Please note the price varies for each region. Let's say the price is $0.20 per GB/month for your region, therefore 0.336GB x $0.20 = $0.0672 to get the price for 1 hour of consuming 250GB of excess backup storage space. We do these steps every hour. 

Likewise, if you would like to understand how much 1hr of consuming 1GB of excess backup storage costs, we can take the example price of $0.20 per GB/month and divide it by 31 days and by 24hr. Therefore $0.20/31 day/24hr = $0.000268817 for consuming 1GB of excess backup storage space for 1 hour of time. Let's verify this in reverse with our previous example of calculating how much 250GB of consuming excess backup storage costs. We'd multiply $0.000268817 for consuming 1GB of excess backup storage space for 1hr with 250GB, therefore $0.000268817 x 250 GB = $0.0672. We get the same price of $0.0672, as in the previous example, for consuming 250GB of excess backup storage space for 1 hour of time.

Azure invoice will only show the excess backup storage used, the part that we charge for. Azure invoice will not show the entire backup storage consumption as it can't show a charge for free backup storage space.

How to monitor backup storage consumption

Backup storage consumption today can be monitored using Azure Cost Management. The tool can be accessed from the Subscription overview blade in Azure portal. You can use filters within the tool to specifically filter out a single managed instance storage consumption if needed. For details see Learn how to monitor backup storage costs for SQL Managed Instance. At this time, monitoring of backup storage space consumption using PowerShell is not available.

Next steps: How to fine-tune backup storage consumption

For recommended steps on fine-tuning backup storage consumption and excess charges, see the next article on Fine tuning backup storage consumption on Managed Instance.

To track when automated backup have been performed on Managed Instance, see How to track the automated backup for an Azure SQL Managed Instance.

Disclaimer

Please note that products and options presented in this article are subject to change. This article reflects the state of backup storage tuning options available for Azure SQL Managed Instance in March, 2021.

Closing remarks

This article was co-authored between Dani Ljepava, Program Manager at Microsoft Azure Data and with Vitor Tomaz, Embedded Escalation Engineer (EEE), SQL Cloud at Microsoft.

If you find this article useful, please like it on this page and share through social media.

To share this article, you can use the Share button below, or this short link: http://aka.ms/mi-backup-explained