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 either the original retention period, or 0 and 1 day 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 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 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 surcharge.
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 1 TB 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) charges at a rate of $/GB/month and provides hour-granular billing. The “hour-granular billing”: means that every hour we check the backup storage consumption and communicate it to the billing system.
Since we charge “$/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 storage, at 10:00am today, for the database was 500GB
This means that at 10:00am today we would need to charge for 250GB of excess usage (since 1x of the instance storage size is free. Therefore, we charge 250GB/31 days/24h = 0,336 GB to transform a monthly charge to an hourly charge. We do these steps every hour
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.
Recommended actions 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.
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 May, 2020.
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.
To share this article, you can use this short link: http://aka.ms/mi-backup-explained