This article is contributed. See the original author and article here.
Many applications have regulatory, compliance, or other business purposes that require you to retain database backups beyond the 7-35 days provided by Azure SQL Managed Instance automated backups. By using the long-term retention (LTR) feature, you can store the specified full backup of a SQL Managed Instance database in Azure Blob storage with configured redundancy for up to 10 years. You can then restore any backup as a new database. Long-term retention is available in Public Preview for Azure SQL Managed Instance.
How long-term retention works?
Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point-time restore (PITR). If an LTR policy is configured, these backups are to different for long-term storage. The LTR policy for each database can also specify how frequently the LTR backups are created. To enable LTR, you can define a policy using a combination of four parameters: weekly backup retention (W), monthly backup retention (M), yearly backup retention (Y), and week of year (WeekOfYear). To learn more about how to configure long-term retention policy, see here.
Configuring backup storage redundancy
By default, SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. When an Azure region is unavailable, geo-restore can be used to recover the database in a different Azure region. Geo-restore is only available if backups are geo-redundant.
However, to meet data residency requirements, managed instance backups can now be configured as local or zone redundant. This ensures that the database backups stay within the same region. The configured backup storage redundancy for a given managed instance applies to both PITR backups and long-term retention backups (LTR).
For SQL Managed Instance, the backup storage redundancy must be selected at the time of creation of the managed instance. Updating backup storage redundancy for an existing managed instance is not supported yet.
Long-term retention using Azure Portal
In this section we will describe how you can configure long-term retention for your SQL Managed Instance database using Azure Portal. Here are the RBAC roles that you will need while operating with the long-term retention backups.
When you setup long-term retention for a database, you may perform the following operations on your backups:
- Set the long-term retention policy for your database.
- Update the long-term retention policy.
- View or get a list of available long-term retention backups.
- Restore from an available long-term retention backup.
- Delete a long-term retention backup.
- Restore from a long-term retention backup of a deleted database.
- Monitor the long-term retention backup costs.
We will look into each of these and understand how this can be done.
Create or Update long-term retention policy for a managed instance database.
To setup a new LTR policy for a database:
- Navigate to the managed instance on Azure Portal.
- Open the Backups tab
- Next, click on the Retention Policies tab and select the database(s) for which you want to set the LTR policy.
- Click on Configure policies.
- Click on ‘Change LTR policies for all selected databases.
- Apply the Weekly/Monthly/Yearly setting that you would like to keep.
- Click Apply to apply the new policy.
List available long-term backups for a database.
To list all the available LTR backups for a given database:
- Navigate to the managed instance.
- Click on Backups -> Available backups
- The databases which have accumulated LTR backups, will show Manage under the ‘Available LTR backups’ column.
- Click Manage, this will open up a list of available LTR backups for the given database. This list shows the time when backup was taken and the expiration time for the backup as per the retention policy.
Restore or Delete a long-term retention backup.
There are 2 different methods that you can use on Azure Portal to restore using a long-term retention backup.
- Go to Available backups blade and for the database where you see Manage under the ‘Available LTR backups’ column, click on the Restore icon. This will open the Create Database/Restore Database experience on a new page.
- OR, you can click on Manage, this will open up a list of available LTR backups in a new blade. Select the LTR backup, which you would like to use for restore. Then click the Restore button. This will also open the Create Database/Restore Database experience on a new page.
3. Clicking on Restore will open the full-screen database restore experience on Azure Portal. Here, you will see the long-term retention source backup. A name for the new database will be pre-selected based on the timestamp, you can change the database name here and set it to desired value.
4. To delete a backup, which is no longer required, instead of clicking Restore, click the Delete button. This will delete the selected long-term retention backup(s). Note that, deleting a long-term retention backup is an irreversible operation. Once deleted, these backups cannot be recovered.
Restore from a long-term retention backup of a deleted database.
To restore LTR backup of a deleted database:
- Navigate to your managed instance on Azure Portal.
- Click on Backups -> Available backups.
- Select the Deleted radio button under Show Databases. This will bring up a list of deleted databases, which have backups available for restore.
- Click Manage under the ‘Available LTR backups’ column. This will open a blade, which will show you a list of available LTR backups for that database.
- Select the backup that you want to restore from the list. Then click Restore. This will open the full-screen restore experience.
Analyze LTR backup costs
To analyze backup costs, you can use the Cost Analysis tool available at the subscription level on Azure Portal. Details of how to look up different backup costs are described here. In this section, we will look at how you can look up your LTR backup costs.
- Navigate to your Subscription on Azure Portal.
- Click on Cost Analysis.
- Click on Add filter.
- Add a ‘Service name‘ filter, with value ‘sql database’.
- Add another filter for ‘Meter subcategory‘, with value ‘ltr backup storage’.
- In the charts displayed, if you slice by Resource, you will see the longtermretetnion backups listed from your resource.
- In the next chart, if you filter by Meter, it will show you the storage type used by backups and the applicable cost for backups.
To meet your organizations compliance or regulatory needs, you can now easily configure long-term retention backups for up to 10 years for Azure SQL managed instance database(s). This can be easily done using Azure Portal or Azure PowerShell. You can also pick the desired backup storage redundancy for backups, to meet your data residency requirements.
If you find this article useful, please like it on this page and share through social media. To share this article, you can use the Share button below, or this short link: https://aka.ms/LTRonMI.
Please note that products and options presented in this article are subject to change. This article reflects the long-term retention backup options available for Azure SQL Managed Instance in March, 2021.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.