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.
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.
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 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:
To enable LTR on your Hyperscale database via Portal, please use following commands:
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
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.
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:
Following are sample PowerShell commands to view LTR backups for a database and restore the backup:
# 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
Following are sample CLI commands to view LTR backups for a database and restore the backup:
az sql db ltr-backup list \ --location eastus2 \ --server myserver \ --database mydb
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)
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.