Fine tuning backup storage costs on Managed Instance

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 keep the retention rate set while database was active, or manually set the retention to 0 or 1 days.*

*Zero (0) days means that no backup is kept for a deleted database.

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.

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:

#Get current configuration of a database

$resourceGroup = ”

$instance = ”

$database = ”

$days = 1

Get-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName $resourceGroup -InstanceName $instance -DatabaseName $database

#Set a new retention on a database (valid backup retention in days must be between 1 and 35)

$resourceGroup = ”

$instance = ”

$database = ”

$days = 1

Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName $resourceGroup -InstanceName $instance -DatabaseName $database -RetentionDays $days
# Set PITR Retention on all Databases

# Valid backup retention in days must be between 1 and 35

$resourceGroup = ”

$instance = ”

$days = 1

Get-AzSqlInstanceDatabase -ResourceGroupName $resourceGroup -InstanceName $instance | Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -RetentionDays $days
# Set PITR Retention on all Deleted Database Backups

# Valid backup retention in days must be between 0 (no retention) and 1

$resourceGroup = ”

$instance = ”

$days = 0

Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroup -InstanceName $instance | Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -RetentionDays $days

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 that databases that do not contain sensitive data (development databases, for instance). This is because backups for the non-encrypted databases are typically compressed with higher compression ratio.

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 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.

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-tuning