In the previous article, we have explained how automated backups on Managed Instance work, how are backups piling up, how you can cause backup pile up and associated costs for excess backup storage. In this article you will learn how to optimize excess backup storage costs.
Fine tuning backup storage costs on 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:
- Optimize database backup retention period
- Maximize your free backup storage space
- Optimize your apps and workloads
- Alternative considerations
Optimize database backup retention period
Default PITR backup retention for active and deleted databases on Managed Instance is 7 days if no action is performed by you. You can manually change the backup retention per database with 1 day granularity. 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.
- For deleted databases, it is possible to set the retention from 0-35 days*
*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.
Please note that setting retention rate lower than 7 days and with 1 day granularity (from 1-35 days for active and 0-35 days for deleted databases) at this time is possible using PowerShell only, and not through Azure portal.
You will need to have Az.SQL module v2.6.0, or higher – update here, to execute these commands. You can also execute the PS 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-azsqlinstancedatabasebackupshorttermretentionpolicy?view=azps-3.1.0
- To get a list of dropped databases: https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqldeletedinstancedatabasebackup?view=azps-3.1.0
- To set a new retention period: https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqlinstancedatabasebackupshorttermretentionpolicy?view=azps-3.1.0
Here are some specific examples on viewing and changing PITR backup retention period for active databases on Managed Instance:
Here are some specific examples on viewing and changing PITR backup retention period for deleted databases on Managed Instance:
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 only to reduce the retention period for the time that is left. For example, if you had a retention period of 7 days for a deleted databases, 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.
Backup storage type currently 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.
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 up to 10 years. Backups made by LTR are used by restoring 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.
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.
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-tuning