Configuring backup storage redundancy in Azure SQL

Database backups are an essential part of any business continuity and disaster recovery strategy, because they protect your data from corruption or deletion. In Azure SQL there are two types of automated backups that customers can use for restoring their databases:



  • Short-term backups used for point-in-time restores (PITR) or geo-restores, keeping backup data for up to 35 days

  • Long-term backups used for configuring longer retentions, keeping backup data for up to 10 years


To protect backup data from planned and unplanned events, including transient hardware failures, network or power outages, and massive natural disasters, backup storage is being replicated to another storage. By default, storage is geo-replicated to a paired region using RA-GRS strategy.


As many applications have regulatory, compliance, or other business requirements for data residency control, geo-redundancy is not good fit for every customer. To overcome this, option for configuring backup storage redundancy has been introduced to Azure SQL Managed Instance (coming soon to Azure SQL DB). It allows customers to choose replication strategy for their backup storages and define if geo-redundancy (RA-GRS), zone-redundancy (ZRS), or local-redundancy (LRS) will be used.


 


What are the differences in storage redundancy?


 


Backup storage redundancy relies on Azure Storage redundancy:



  • Locally redundant storage (LRS)

    • Design characteristics: replicates your data three times within a single physical location in the primary region. LRS provides at least 99.999999999% (11 9’s) durability of objects over a given year. LRS protects your data against server rack and drive failures. However, if a disaster such as fire or flooding occurs within the data center, all replicas of a storage account using LRS may be lost or unrecoverable.

    • Best for: LRS keeps your data in the same region and provides capability of data residency and helping you to stay compliant with regulatory requirements. In addition, LRS is the lowest-cost redundancy option (but offering the least durability compared to other options) which is good fit for dev/test scenarios.



  • Zone-redundant storage (ZRS)

    • Design characteristics: replicates your Azure Storage data synchronously across three Azure availability zones in the primary region. Each availability zone is a separate physical location with independent power, cooling, and networking. ZRS offers durability for Azure Storage data objects of at least 99.9999999999% (12 9’s) over a given year.

    • Best for: ZRS also provides capability of data residency but offers higher durability due to data replicated across availability zones. It is good fit for production scenarios that are cost sensitive.



  • Geo-redundant storage (RA-GRS) – RECOMMENDED (DEFAULT)

    • Design characteristics: replicates your data synchronously three times within a single physical location in the primary region using LRS. It then copies your data asynchronously to a single physical location in a secondary region that is hundreds of miles away from the primary region. RA-GRS offers durability for Azure Storage data objects of at least 99.99999999999999% (16 9’s) over a given year.

    • Best for: RA-GRS is best disaster recovery option which gives highest durability. In addition, geo-redundant backup storage enables Geo-restore capability – a cheap and economically efficient disaster recovery option. This is default configuration value and if there is no need for data residency compliance, it is recommended to use RA-GRS backup storage for all production workloads.




While LRS and GRS are available in all regions, ZRS is available only in specified regions. Detailed pricing information can be found on Azure SQL Managed Instance pricing page and Azure SQL DB pricing page.


 


Feature capabilities


 


Backup storage redundancy option is in Preview phase. Main capabilities are following:



  • Redundancy can be configured using REST API, PowerShell, Azure CLI, ARM template or Azure Portal

  • Available redundancy options are LRS, ZRS and RA-GRS

  • When configured redundancy is applied for both PITR and LTR backups

  • Geo-Restore functionality is available only for instances with configured RA-GRS backup storage redundancy


Managed instance limitations:



  • Redundancy can be configured only during managed instance creation and cannot be changed later

  • Redundancy is applied at instance level and cannot be configured per individual managed database


How can I configure backup storage redundancy?


 


Backup storage redundancy can be configured during creation when request is submitted using REST API, PowerShell, Azure CLI, ARM template or Azure Portal. In official documentation page, you can find instructions on how to select backup storage redundancy.


 


How can I change backup storage redundancy for existing managed instances?


 


It is not possible to update backup storage redundancy for existing instances. However, there is workaround which relies on process of creating new managed instance with different redundancy and moving your databases from old to the new instance.


Steps:



  1. Create new managed instance and select desired backup redundancy

  2. Use cross-instance point-in-time restore, transactional replication or use .bacpac files to backup and restore your data using SSMS

  3. Delete old managed instance


 


How long are backups kept on deleted managed instance?


Backups are kept until retention period set for each database expires + 7 days. For more details visit Backup storage consumption on Managed Instance explained. If you have need for immediate removal of backup data stored on old instance before deleting it you can:



  • For LTR delete all backups taken and turn off LTR settings (learn how-to)

  • For short-term backups reduce retention of active databases to 1 day and deleted databases to 0 days (learn how-to)


These two actions will ensure that all your data is removed from the old managed instance in up to 8 days.


 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.