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 . In this article you will learn optimize excess backup costs.

Fine tuning backup 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, considerdecreasing the retention periodto 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 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:

Here are some specific examples on viewing and changing PITR backup retention period for active databases on Managed Instance:

PowerShell
#GET PITR backup retention for an active individual database
$resourceGroup = '' $instance = '' $database = '' Get-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName $resourceGroup -InstanceName $instance -DatabaseName $database #SET new PITR backup retention period on an active individual database
# Valid backup retention must be between 1 and 35 days $resourceGroup = '' $instance = '' $database = '' $days = 1 Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName $resourceGroup -InstanceName $instance -DatabaseName $database -RetentionDays $days # SET new PITR backup retention period for ALL active databases
# Valid backup retention must be between 1 and 35 days $resourceGroup = '' $instance = '' $days = 1 Get-AzSqlInstanceDatabase -ResourceGroupName $resourceGroup -InstanceName $instance | Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -RetentionDays $days

Here are some specific examples on viewing and changing PITR backup retention period for deleted databases on Managed Instance:

PowerShell
# GET PITR backup retention for an individual deleted database
$resourceGroup = ''
$instance = ''
$database = ''
Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroup -InstanceName $instance -DatabaseName $database | Get-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy
# GET PITR backup retention for ALL deleted databases $resourceGroup = '' $instance = ''
Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroup -InstanceName $instance | Get-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy
# SET new PITR backup retention on an individual database
# Valid backup retention must be between 0 (no retention) and 35 days. Valid retention rate can only be lower than the period of the retention period when database was active, or remaining backup days of a deleted database
$resourceGroup = '' $instance = ''
$database = ''
$days = 0
Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroup -InstanceName $instance -DatabaseName $database | Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -RetentionDays $days
# SET new PITR backup retention for ALL deleted databases
# Valid backup retention must be between 0 (no retention) and 35 days. Valid retention rate can only be lower than the period of the retention period when database was active, or remaining backup days of a deleted database $resourceGroup = '' $instance = '' $days = 0 Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroup -InstanceName $instance | Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -RetentionDays $days
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 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 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 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 for databases that do not contain sensitive data (for instance development databases). This is because backups for non- databases are typically compressed with a 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 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.

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

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.