This article explains Azure SQL Database Geo Restore using geo-replicated backups for disaster recovery.
Introduction
Any business continuity and disaster recovery plan must include database backups to protect your data from corruption and loss. Azure database backups allow point-in-time recovery within the configured window. By default, it has 35 days retention period, but you can extend up to 10 years using long-term retention for single and pooled databases.
The backup frequency for Azure SQL Database and Managed Instance (MI) is as below.
- Full back up every week
- Differential backup every 12-24 hours
- Transaction log backup every 5-10 mins
Once you deploy an Azure SQL Database, it asks you to specify backup storage redundancy.
The supported values for backup storage redundancy are below.
- Locally redundant backup storage
- Zone-redundant backup storage
- Geo-redundant backup storage
Before we explore the storage redundancy, let’s understand the term – Paired Region.
Paired Region
Azure regions are set of multiple datacenters grouped in the geographic region. These data centers connect with low latency networks and high throughput to ensure high performance and security.
The regional pair contains two regions within the same geography. Azure ensures that at least one region is available during any planned maintenance.
Note: All diagrams in the article are references from Microsoft docs.
Few specific Azure Services use the paired region for business continuity and protecting data loss. For example, Azure Geo-Redundant Storage (GRS) replicates data from a primary to a secondary region. It ensures that you don’t lose data if the primary region is not recoverable.
To get a list of Azure paired regions, refer to the article https://docs.microsoft.com/en-us/azure/best-practices-availability-paired-regions.
The following diagram shows a representation of the hypothetical Azure regional pair.
Backup storage redundancy
In the default configuration, the Azure SQL Database configuration stores data in the geo-redundant storage blobs. These storage blogs replicate data to the paired region. Therefore, if an outage occurs in the primary region, you can restore the backups in a different region.
Let’s explore the different options available for backup storage redundancy.
Locally-redundant storage
The locally redundant storage replicates data three times in the primary region within the data center. It provides 99.999999999%(11 nines) durability of objects in the given year. It protects data from server rack and drives failures. It writes data synchronously among the three replicas. The locally redundant storage provides owest-cost redundancy and durability.
Zone-redundant storage
The Zone redundant storage (ZRS) replicates data across three availability zones in the primary region. These availability zones consist of separate physical locations, power, cooling, networking. Azure provides 99.9999999999(12 9’s) durability over a given year.
The ZRS writes data to the availability zone storage copies synchronously. It means that once the data is written to all data copies across availability zones, the write operation is successful.
The ZRS is suitable for high availability with excellent performance, low latency.
Note: The Zone redundant storage is not available across all regions. You can navigate to https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy for a list of supported regions.
Geo-redundant storage
As shown in the diagram above, the Geo-redundant storage(GRS) does the following:
- It uses locally-redundant copies (LRS) and synchronizes data three times in the primary region
- It copies data to a secondary physical region asynchronously, and the secondary region is situated hundreds of miles away from the primary region
- The secondary region also uses LRS and maintains three copies of data
The Geo-redundant storage allows you to read or write data even if the entire region is unavailable. If the entire primary region has an outage or disaster, you can retrieve data from the secondary region. It provides 99.99999999999999% (16 9’s) durability over a given year.
Geo-restore in Azure SQL Database
Azure SQL Database enables users to restore a database to another(any) Azure region using geo-replicated backups. You can restore the backups to another region if the Azure SQL DB is configured with the Geo-redundant backup storage.
The geo-restore is the default recovery method if the database is unavailable in the primary (hosting) region. As sown in the below image, due to region outage in US East, you can restore the last geo-replicated backup to any existing or new Azure server.
The above image reflects the restoration of the Azure database in the secondary region due to an outage in the hosting replica.
Note:
- The geo-restore works even if the data center or database in the hosting region is unavailable
- There might delay(up to 1 hr.) between a database backup and its replication to the secondary region
Use Azure portal for Geo-restore
This section uses the Azure portal and specifies the steps for geo restore database in a region apart from the hosting region.
In my demo environment, the Azure database is in Central India.
For testing the geo-restore, I create a new Azure SQL Database in the US (East) region. In the Azure portal, search for SQL Database and launch Create SQL Database page.
In the database details, select or create a new Azure Server in East US.
Navigate to the additional settings page and choose backup in the use of existing data.
Once you click on the backup option, it gives you a list of databases available for restores. As you can see, in my case, it displays [azuredemosqldemo] database hosted in Central India, and we want to restore it in East US.
It displays the latest backup available for the geo-restore.
Select the backup, and you get a prompt stating that selecting this backup will modify the compute+ storage in basics. It sets the compute and storage equivalent to the source Azure database. You can scale up the resource post-restoration.
Click OK, review your configurations, and click on deploy to proceed with geo restore in the Azure region.
Use Azure PowerShell script for geo-redundant backup of a database
We can use the Azure PowerShell command – Get-AzSqlDatabaseGeoBackup – to get details of geo-redundant backup of Azure SQL Database.
Launch Azure Cloud Shell in Azure portal and configure for your local server using the article https://www.sqlshack.com/an-overview-of-azure-cloud-shell/
The Get-AzSqlDatabaseGeoBackup requires resource name and Azure server name, and it returns information about all databases present in the Azure server.
Get-AzSqlDatabaseGeoBackup -ResourceGroupName “MyAzureLabSQL” -ServerName “azuredemosqldemo”
As shown below, it returns the Azure database, edition, and last available backup for geo restore. You can note that it is impossible to specify a backup other than the last available backup (recent) for geo restore.
To perform the geo restore using Azure PowerShell, use the command – Restore-AzSqlDatabase.
- The Get-AzSQLDatabaseGeoBackup returns the information of geo-redundant backup for the database – Database01.
- The second command, Restore-AzSqlDatabase, restores geo backup in the target Azure server with the database name – RestoredDatabase.
$GeoBackup = Get-AzSqlDatabaseGeoBackup -ResourceGroupName “ResourceGroup01” -ServerName “Server01” -DatabaseName “Database01”
Restore-AzSqlDatabase -FromGeoBackup -ResourceGroupName “TargetResourceGroup” -ServerName “TargetServer” -TargetDatabaseName “RestoredDatabase” -ResourceId $GeoBackup.ResourceID -Edition “Standard” -ServiceObjectiveName “S2”
Geo-Restore limitations
The geo-restore feature in Azure SQL Database and Managed Instance has the following limitations.
- It does not support performing a point-in-time recovery on the secondary Azure region.
- The Geo-Restore provides a basic disaster recovery mechanism and relies on geo-replicated backups.
- The geo-replicated backups have a recovery point objective (RPO) of up to 1 hour and a recovery time of up to 12 hours.
- IN case of a region outage, it does not guarantee to restore capability due to a sharp increase in demand for resource deployment in the secondary region.
- It is suitable for relatively small and less critical databases that can afford data loss from the last geo-replicated backups.
- It is always recommended to use Auto Failover Groups for business-critical applications. It offers lower RPO and RTO with guaranteed data availability.
Conclusion
The article explores Azure SQL Database Geo Restore that can restore the latest backup in any Azure region. Therefore, if your instance is not available due to an incident, you may choose to recover database and application connectivity.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023