We are excited to announce Preview of short-term backup retention up to 35 days in the Hyperscale tier of Azure SQL Database. Before this improvement, Hyperscale supported backup retention up to 7 days, with this preview feature you can configure backup retention within 1-35 days and hence do point-in-time restore to any time within the configured retention.
Hyperscale Backup Overview
Hyperscale tier of Azure SQL Database does not use traditional full, differential, and transaction log based backup architecture. Instead, regular storage snapshots of data files are taken and are nearly instantaneous. Generated transaction log is retained as-is for the configured retention period. At restore time, relevant transaction log records are applied to the restored storage snapshots, resulting in a transactionally consistent database without any data loss as of the specified point in time within the retention period.
How to configure Short-term Retention
Short term retention for Hyperscale databases can be configured via Portal, PowerShell, Azure CLI and REST API. Existing portal experience, PowerShell and CLI commands have been enhanced to support this feature.
Portal:
- Navigate to Backups page of SQL Server that hosts your Hyperscale database
- Click on “Retention Policies” tab to view list of all available databases
- Select database and click on “Configure policies” to modify retention.
- Configure desired retention(up to 35 days) using the slider option and click on Apply button.
- Verify the retention configuration for the database is updated in the “Retention Policies” tab
PowerShell:
PowerShell commands that you currently use to configure backup retention have been enhanced to support new backup retention limits. You can use Set-AzSqlDatabaseBackupShortTermRetentionPolicy to configure short-term retention of an existing hyperscale database. Here is an example PowerShell command to configure short-term:
Set-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName resourcegroup01 -ServerName server01 -DatabaseName database01 -RetentionDays 35
CLI:
Similar to PowerShell, CLI commands that you currently use to configure backup retention have been enhanced to support new short-term retention limits. You can use az sql db str-policy test command to configure short-term retention. Here is an example Azure CLI command to configure STR:
az sql db str-policy set -g mygroup -s myserver -n mydb --retention-days 35
Billing for short-term backup retention
Starting 4 May 2022, all newly created Hyperscale service tier databases of Azure SQL Database will be billed for short-term backup storage. Any Hyperscale databases created before 4th May 2022 will be billed for short term backup storage only if the retention period is increased beyond 7 days. Backup storage for Hyperscale databases is charged based on the Data backup storage size and Log backup storage size.
Charges for short term backup storage will be reflected in your June 2022 statement.
Monitor Hyperscale backup storage consumption
In Hyperscale, Data storage size, Data backup storage (snapshot backup size) and Log backup storage (transactions log backup size) are reported via Azure Monitor metrics. Here are the instructions to view backup, data storage and log backup size metrics in Azure Portal:
- Navigate to the Hyperscale database for which you’d like to monitor backup and data storage metrics
- Navigate to Metrics page in the Monitoring section
- From the Metric drop down list select Data backup Storage, Data storage size and Log backup Storage metrics with appropriate aggregation rule
Hyperscale backup storage costs
Backup storage cost of a Hyperscale service tier database depends on the choice of region, backup storage redundancy and workload type. Write heavy workloads are more likely to change data pages frequently, which results in larger data backup storage size as backups in Hyperscale are taken with snapshots of storage. Such workloads also generate more transaction log, contributing to the overall backup costs. Backup storage is charged per GB/month consumed, for pricing details see [Azure SQL Database pricing](https://azure.microsoft.com/pricing/details/sql-database/single/) page.
For Hyperscale database, billable backup storage is calculated as follows:
Total billable backup storage size = (Data backup storage size + Log backup storage size)
Data storage size is not included in the billable backup as it is already billed as allocated database storage.
Please note Data backup storage size is indicative of modified data and is not a full or differential backup.
Optimize Backup Storage Consumption
Backup storage consumption for a Hyperscale service tier database depends on the retention period, choice of region, backup storage redundancy and workload type. Consider some of the following tuning techniques to reduce your backup storage consumption:
- Reduce the backup retention period to the minimum possible for your needs.
- Avoid doing large write operations, such as index maintenance, more frequently than you need to. For index maintenance recommendations, see Optimize index maintenance to improve query performance and reduce resource consumption.
- For large data load operations, consider using data compression when appropriate.
- Use the `tempDB` database instead of permanent tables in your application logic for storing temporary results and/or transient data.
- Use locally redundant or zone redundant backup storage when geo-restore capability is unnecessary (for example: dev/test environments).
Migration Experience
If you are migrating your Business-Critical or General-Purpose databases to Hyperscale service tier, your current short-term retention settings in Business Critical/General Purpose tiers will be migrated to your Hyperscale databases as well. if you wish to have a different configuration on Hyperscale database, you can modify the STR settings using same instructions as mentioned above.
For more details on Azure SQL Database Hyperscale short-term backup retention please refer to Hyperscale backups documentation
