In the previous article, we have explained how automated backups on Azure SQL Managed Instance work, how backups piles up, and some of the ways you contribute to such pile up. We have also explained how associated costs for excess backup storage are calculated. In this article you will learn how to optimize your backup storage consumption and reduce your excess costs.
Fine tuning backup storage costs on SQL Managed Instance
To reduce the billing charges for excess usage of the backup storage space beyond the free backup storage space provided, you can control the backup consumption using these general approaches:
- Choosing the backup storage type that is right for you
- Optimize database backup retention period
- Maximize your free backup storage space
- Optimize your apps and workloads
- Alternative considerations
Choosing the backup storage type that is right for you (NEW Aug-2020)
As of August 2020 we have added a new option and more choices to customers - ability to choose one of the three available backup storage types, each with different redundancy options and priced differently. Choice of the backup storage type on will influence your overall backup storage costs. Previously, we have offered only a single backup storage type (RA-GRS), and at this time we are offering two more, and less expensive storage types (LRS and ZRS).
The choices customers can make between the three storage types offered are:
| Backup storage type choice | Option | Cost vs. protection | Notes |
| 1. Geo-redundant storage (RA-GRS) | Default option (existing option since GA of MI) |
Most expensive, provides maxim protection of 3 backup copies in your primary and 1 additional backup copy in a secondary region. Allows Geo-Restore of backups in another region, even if your primary region is down. | Best suited for globally scaled applications requiring the most protection. |
| 2. Zone-redundant storage (ZRS) |
New option (Aug 2020) |
Less expensive, provides redundancy protection of 3 backup copies across availability zones in your primary region. | It can be used for data-residency compliance in cases of strict restrictions on data exiting the primary region. |
| 3. Locally redundant storage (LRS) |
New option (Aug 2020) |
Cheapest option, provides a single backup copy in your primary region. | It can be used for data-residency compliance in cases of strict restrictions on data exiting a single data center. |
Detailed pricing information on each storage type can be found on Azure SQL Managed Instance pricing page. LRS and GRS backup storage options are available in all regions, while ZRS is available only in specific regions.
The storage type can be chosen only at the time of creating a new Managed Instance. It is not possible to change the backup storage type once Managed Instance has been created. However there is a workaround in creating a new MI with different storage option and transferring data across. For deeper technical details about all three storage types, and workaround in case of an existing MI, see Configuring backup storage redundancy in Azure SQL Managed Instance for details.
Once you have provisioned a Managed Instance with one of the three backup storage types that work the best for you, use the techniques described below to optimize your backup storage costs.
Optimize database backup retention period (NEW - May 2020)
Default PITR (Point In Time Restore) backup retention for active and deleted databases on managed instance is 7 days if no action is performed by you. We have added a new option in Feb-2020 allowing you to manually change the backup retention for active databases with 1 day granularity. In May-2020 we have added a new option to manually change the retention period for deleted databases with also 1 day granularity, and an option to remove all backups of a deleted database by setting the retention period to 0 days. Configuring PITR backup retention is database scoped, therefore it can be separately set for each database on a Managed Instance. The following PITR backup retention periods are available for databases on managed instance:
- For active databases, it is possible to set the retention from 1-35 days using Azure Portal and PowerShell.
- For deleted databases, it is possible to set the retention from 0-35 days* using PowerShell only.
*Zero (0) days means that no backup is kept for a deleted database, and that such backup is permanently deleted. Once you reduce backup retention period of a deleted database to a lower value, it is no longer possible to increase it.
Review the backup retention period of your active databases on managed instances and optimize the consumption in accordance to your needs. If you’ve extended the backup retention rate beyond the default of 7 days, consider decreasing the retention period to the lowest possible value (1 day) to use less storage and optimize your costs. You also might want to consider reducing the backup storage retention for deleted databases to 1 day or remove them entirely with 0 days retention.
| Important |
|
Please note that setting PITR backup retention rate from 1-35 days for active databases is possible using Azure Portal and PowerShell, and that setting PITR backup retention rate from 0-35 for deleted databases is possible only through PowerShell.
To use PowerShell, you will need to have Az.SQL module v2.6.0, or higher - update here, to execute commands provided in this article. You can also execute the PowerShell commands through Azure CloudShell in the portal which is always up to date with the latest Az.SQL module. |
The following commandlets are available to you to view and change backup retention period for active and deleted databases:
- To get retention period policies set for the databases: https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqlinstancedatabasebackupshorttermre...
- To get a list of dropped databases: https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqldeletedinstancedatabasebackup?vie...
- To set a new retention period: https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqlinstancedatabasebackupshorttermre...
Configure backup retention period for active SQL Managed Instance databases using Azure Portal
To change the PITR backup retention period by using the Azure portal, go to the server or managed instance with the databases whose retention period you want to change. Changes to PITR backup retention for SQL Managed Instance are done at an individual database level. To change PITR backup retention for an instance database from the Azure portal, go to the individual database overview blade. Then select Configure backup retention at the top of the screen:
Configure backup retention period for active SQL Managed Instance databases using PowerShell
Here are some specific examples on viewing and changing PITR backup retention period for active databases on managed instance:
| PowerShell |
|
Configure backup retention period for deleted SQL Managed Instance databases using PowerShell
Here are some specific examples on viewing and changing PITR backup retention period for deleted databases on managed instance:
| PowerShell |
|
| Note |
|
Once you decrease the backup retention period of a deleted database to a lower value, it is no longer possible to increase it. For example, if the default backup retention rate for a deleted database was 7 days, and you have decreased it to 3 days, it is no longer possible to increase the backup retention period. Also, if some time has passed since you deleted a database, you will be able to reduce the retention period only for the time that is left. For example, if you had the backup retention period of 7 days for a deleted database, 5 days later you are trying to set the retention rate to 6 days, you will not be able to do that as you have only 2 days of retention remaining for that database. |
If you need to keep a database beyond the available retention period, you would need to restore it to an active database before the backup retention expires. Once the backup retention of a deleted databases expires, it is permanently removed, and it is no longer possible to restore such database.
Maximize your free backup storage space
Consider increasing data storage space on General Purpose (GP) managed instances to get more of the free backup storage space.
Default backup storage type used on managed instance is read-access geo-redundant storage (RA-GRS) to ensure the backups are preserved even if the data center is unavailable. While this type of storage is great for disaster recovery scenarios, it comes at cost. For managed instances in General Purpose (GP) service tier, the provisioned storage for data is about half the price of the backup storage space. Customers with constant backup storage costs on GP service tier may increase the provisioned storage in order to save on the backup storage.
For example, if you have provisioned 4TB of data storage space on GP managed instance, and have determined through the Cost Management center that you are using 5-8TB of backup storage space, it would be more effective for you to provision 8TB of data storage space on managed instance, as you will automatically get 8TB of backup storage space for free. This is because the free backup storage space provided for a managed instance is equal to the data storage space provisioned. As data storage space is is less expensive, although you will not be using all of it, it would be more worth for you to increase it to save on the backup storage costs.
Optimize your apps and workloads
To further fine tune backup storage consumption for your databases on managed instance, consider fine-tuning your applications and workloads as follows:
- Avoid performing large write operations more frequently than needed, such are index rebuilds
- For large data load operations (especially for analytical data mart \ data warehouse workloads), consider using clustered columnstore indexes, reduce number of non-clustered indexes, and do bulk load operations with row count around 1 million.
- Use TempDB in your ETL logic for storing temporary results, instead of permanent tables.
- Consider turning off TDE encryption for databases that do not contain sensitive data (for instance development databases)**. This is because backups for non-encrypted databases are typically compressed with a higher compression ratio.
**As of July 2020 we have made additional improvements to compression of TDE encrypted databases with up to 30% improvement in using less of the backup storage space.
Alternative Considerations
In some cases where no frequent ability to restore database is needed, you can consider using less expensive LTR (long-term retention), currently in limited public preview for managed instance. In such case, you can reduce the PITR backup retention for your active databases to a minimum required, or having no backup (0 day retention) for deleted database, and consider using LTR. The LTR allows backup retention for up to 10 years. Unlike PITR backups which do not persist upon deletion of a managed instance, LTR backups will be retained within the retention period configured even after a managed instance has been deleted. Backups made with LTR are used through the means of restoring a backup to a new database when needed.
To learn more about LTR, see How SQL Database long-term retention works. To use LTR for managed instance, you must request to be enrolled into a limited public preview through Azure support ticket, see Enroll into LTR limed public preview for Managed instance.
Product improvement history
This to provide a history of improvements our team has delivered to help customers fine-tune backup storage costs on Managed Instance:
- Feb-2020 - Introduced option to reduce backup retention for active databases to min. 1 day (from 7 days before)
- May-2020 - Introduced option to reduce backup retention for deleted databases to min. 0 days/no backup (not existing before)
- July-2020 - Introduced a better backup compression for up to 30% for TDE encrypted databases
- Aug-2020 - Introduced less expensive ZRS and LRS backup storage type options (not existing before)
Related resources
To find out how the backup billing charges for MI are calculated, see Backup storage consumption on Managed Instance explained. To learn how to track automated backups on managed instance, see How to track automated backups on Managed Instance.
Disclaimer
Please note that products and options presented in this article are subject to change. The article was originally published in May, 2020 and was updated thereafter. This article reflects the state of backup storage tuning options available for Azure SQL Managed Instance in September, 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.
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-tuning