We are excited to announce gated preview of Long-term backup retention (LTR) feature for Azure SQL Hyperscale databases. In this blog we are going to discuss how Hyperscale LTR feature works and how to enable LTR on Azure SQL Hyperscale databases.
Many applications have regulatory, compliance, or other business requirements that require the database backups to be stored for long duration, beyond 1-35 days duration provided by Automatic backups also known as PITR backups. Long-term backup retention feature helps users meet these requirements by storing database backups for up to 10 years. Long-term backup retention feature is currently available for all service tiers of Azure SQL Database including Basic, Standard and Premium tiers in DTU model and General Purpose, Business Critical service tiers in vCore based model, except for Hyperscale service tier. We are happy to announce LTR feature is now available for preview in Hyperscale tier as well.
Hyperscale database & Long-term retention
Unlike other service tiers in Azure SQL Database, Hyperscale databases use a unique architecture with highly scalable storage and compute performance tiers. This separation of Storage and Compute enables Hyperscale to push down backup and restore operations to the storage layer and eliminates any resource consumption on compute replicas due to backup operations. Backups on Hyperscale are snapshot based and hence backups are nearly instantaneous and restore operations within the same Azure region typically finish in minutes. Since backups are snapshot based, database and backups share same storage account and hence the chosen backup storage redundancy is applicable for data as well as backups. More details on backup and restore architecture in Hyperscale.
Long-term retention on Hyperscale databases leverages the snapshots taken to enable point-in-time-restore (PITR) and copies the snapshots to different blobs for long-term storage. The copy operation happens at a storage level and doesn’t utilize resources from compute and hence do not impact any ongoing write or read operations.
Limitations
NOTE: LTR on Hyperscale is in a limited public preview phase. Databases created after June 2022 are on the new storage architecture and LTR can be enabled. Hyperscale databases created prior to June 2022 use old storage architecture and we are migrating them to the new architecture. Your database is using old storage architecture, if you encounter this error: “Long Term Retention is not supported: Long-term retention on Hyperscale is currently in limited preview and cannot be enabled as yet for your database. To enable long-term retention please reach out to Microsoft support.” We intend to migrate all databases to new architecture over the next few months. However, if you want to enable LTR on your Hyperscale database right away, please raise a support ticket and Microsoft will guide you through next steps for migration. Please note, storage migration process will involve a down time of 2-10 minutes depending on the size of the database. Down time happens during normal patching window. For more information on normal patching window please refer to: https://learn.microsoft.com/en-us/azure/azure-sql/database/maintenance-window-configure
How to configure LTR on Hyperscale databases
On Hyperscale, though the underlying backup architecture differs from other service tiers, experience of configuring long-term retention is at par with other service tiers. Users can define LTR policy using combination of four parameters: weekly backup retention (W), monthly backup retention (M), yearly backup retention (Y), and week of year (WeekOfYear).
- If you specify W, one backup every week will be copied to the long-term storage.
- If you specify M, the first backup of each month will be copied to the long-term storage.
- If you specify Y, one backup during the week specified by WeekOfYear will be copied to the long-term storage. If the specified WeekOfYear is in the past when the policy is configured, the first LTR backup will be created in the following year.
If you specify a value for W, one backup every week will be copied to the long-term storage. If you specify a value for M, the first backup of each month will be copied to the long-term storage. If you specify a value for Y, one backup during the week specified by WeekOfYear will be copied to the long-term storage. If the specified WeekOfYear is in the past when the policy is configured, the first LTR backup will be created in the following year. Below are the instructions to enable LTR on your Hyperscale database:
Portal
To enable LTR on your Hyperscale database via Portal, please use following commands:
- In the Azure portal, navigate to your server and go to Backups section. Select the Retention policies tab to modify your backup retention settings.
- On the Retention policies tab, select the database(s) on which you want to set or modify long-term backup retention policies. Unselected databases will not be affected.
- In the Configure policies pane, specify your desired retention period for weekly, monthly, or yearly backups. Choose a retention period of '0' to indicate that no long-term backup retention should be set.
- Select Apply to apply the chosen retention settings to all selected databases.
PowerShell
Below are sample PowerShell commands to enable LTR on Hyperscale database:
# get the SQL server
$subId = "<subscriptionId>"
$serverName = "<serverName>"
$resourceGroup = "<resourceGroupName>"
$dbName = "<databaseName>"
Connect-AzAccount
Select-AzSubscription -SubscriptionId $subId
$server = Get-AzSqlServer -ServerName $serverName -ResourceGroupName $resourceGroup
# create LTR policy with WeeklyRetention = 12 weeks. MonthlyRetention and YearlyRetention = 0 by default.
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName -ResourceGroupName $resourceGroup -WeeklyRetention P12W
# create LTR policy with WeeklyRetention = 12 weeks, YearlyRetention = 5 years and WeekOfYear = 16 (week of April 15). MonthlyRetention = 0 by default.
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName -ResourceGroupName $resourceGroup -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 16
Azure CLI
Below are sample Azure CLI commands used to enable LTR on Hyperscale:
az sql db ltr-policy set \
--resource-group mygroup \
--server myserver \
--name mydb \
--weekly-retention "P12W" \
--yearly-retention "P5Y" \
--week-of-year 16
This example sets a retention policy for 12 weeks for the weekly backup, 5 years for the yearly backup, and the week of April 15 in which to take the yearly LTR backup.
Restore from an LTR backup
Since LTR on Hyperscale is also snapshot based, restore operation completes in minutes rather than hours or days. Here are the ways to restore LTR backups:
Portal
- In the Azure portal, navigate to your server and then select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane will appear with a list of the available LTR backups for the selected database.
- In the Available LTR backups pane that appears, review the available backups. You may select a backup to restore from or to delete.
- To restore from an available LTR backup, select the backup from which you want to restore, and then select Restore.
- Choose a name for your new database, then select Review + Create to review the details of your Restore. Select Create to restore your database from the chosen backup.
- On the toolbar, select the notification icon to view the status of the restore job.
- When the restore job is completed, open the SQL databases page to view the newly restored database.
PowerShell
Following are sample PowerShell commands to view LTR backups for a database and restore the backup:
- List all available LTR backups and select the backup you want to restore
# get the LTR backups for a specific database from the Azure region under a named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $ServerLocation -ServerName $serverName -DatabaseName $dbName
If the above parameter lists multiple backups, please note the ResourceId of the backup you want to restore.
# restore a specific LTR backup as an P1 database on the server $serverName of the resource group $resourceGroup
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackups.ResourceId -ServerName $serverName -ResourceGroupName $resourceGroup -TargetDatabaseName $dbName -ServiceObjectiveNameHS_Gen5_2
CLI
Following are sample CLI commands to view LTR backups for a database and restore the backup:
- List all available LTR backups for a given database
az sql db ltr-backup list \
--location eastus2 \
--server myserver \
--database mydb
- Get the backup-id of the LTR backup you want to restore by passing the name of the backup as a parameter:
get_backup_id=$(az sql db ltr-backup show
--location eastus2 \
--server myserver \
--database mydb \
--name "3214b3fb-fba9-43e7-96a3-09e35ffcb336;132292152080000000" \
--query 'id' \
--output tsv)
- Restore your database using LTR backup
az sql db ltr-backup restore \
--dest-database targetdb \
--dest-server myserver \
--dest-resource-group mygroup \
--backup-id $get_backup_id
Other operations that can be performed on the LTR backups include View LTR Policies, View LTR backups and Delete LTR backups.
Resources
More information on how to perform these operations can be found in Configure LTR Backup Retention documentation.
More details on Long-term backup retention can be found in Long-term retention overview