Prashanth Jayaram
Create Azure SQL Database using Azure portal to restore the geo backup

Quick start guide to Geo-restore in Azure SQL Database

November 24, 2020 by

Azure SQL Database—a cloud-based service model provides a platform to back up the data and ensure to keep the business up-and-running even after the disaster. The data is vital and backup of the data revolves around the process of backup, restoration, recovery, Business-Continuity-Plans (BCP), and disaster recovery (DR). In this article, we will learn:

  1. Azure SQL Database business continuity plan and disaster recovery solution
  2. Definitions of RPO and RTO
  3. Discuss Azure SQL database automatic database backup
  4. Explain Geo-restore with examples
  5. And more…

Introduction

Azure SQL Database provides an in-built high-availability and fault-tolerance solution to handle business continuity and disaster recovery scenarios. In addition, there are several configurable parameters that can achieve high-availability/disaster recovery across multiple regions.

High-availability and disaster-recovery planning are vital to business-continuity-plans. In this article, we will explore the Azure extend to high availability and disaster recovery solution. By default, Azure offers a Geo-restore feature—the most basic disaster-recovery solution available for Azure SQL Database and Azure SQL Managed Instance. The automatic geo-replicated backups define the recovery-point-objective (RPO) up to 1 hour and recovery-time-objective (RTO) up to 12 hours.

  • Note 1: If the application is critical and business availability is of utmost importance, then Geo-restore might not be a good option. If the application is non-critical, and relatively small and the business can withstand the longer recovery paths then geo-restore may be a relevant disaster-recovery solution. In other words, for critical applications and large databases and must ensure business continuity and disaster-recovery strategy then using active geo-replication is a viable option. The active geo-replication setup uses Auto-failover-groups and provides the lower recovery-point-objective and recovery-time-objective, and there is no limitation on the target region.
  • Note 2: If you are using geo-restore as the disaster recovery solution then the recovery point objective usually takes place within the first 12 hours from the point of restore command invocation. In addition, you may also see a data loss up to 1 hour and it depends on when the last backup was initiated and completed, and replicated to the target region
  • Note 3: During the recovery process, the database can’t be accessed. It neither records the transactions nor responds to the queries
  • Note 4: During the recovery process, if the data-center comes back online, it is easy to cancel the recovery process

Azure SQL database backup

Database backups are also a vital part of any organization’s business-continuity-plan and disaster-recovery strategy. Azure SQL offers two types of automatic backups based on the performance models:

  • First, short-term backups: you can perform point-in-time-restore or geo-restore based on the availability of the backup. By default, Azure stores seven days of database backups in the storage using the RA-GRS technique. The storage is geo-replicated to azure-paired-region. In addition, the retention period can be customizable for up to 35 days. We can adjust the number of retention days based on the requirement. This is also a key parameter in strategizing your storage cost optimization. There is no additional cost for backup storage until it goes beyond 100% of the provisioned database storage. For example, the database provisioned size is 250 GB, then there is no charge incurred for 250 GB of backup. After that, they will charge it $0.20/GB/month. The storage is used to maintain weekly full, daily-hourly differential and every 5-10 minutes t-log backups are initiated
  • Second, long-term backup policy—sometimes you may need to configure and safeguard your backup for longer retentions to meet the security compliance and regulatory bodies. Here, you can keep the database backup for up to 10 years
  • Note: Sometimes, geo-redundancy may not be a good fit to meet the application requirement, compliance, and government regulatory bodies. To overcome this problem, azure provides an option to configure storage redundancy. As of writing this article, this option may not available across all the regions—some regions are launched with a storage preview feature. With this option, the customers can choose the storage replication strategy to meet their business needs. Besides the default configuration (RA-GRS), you can configure it to LRS (Local Redundant Storage) and ZRS (Zone Redundant Storage)

Demo time

Before getting started, log in to the Azure account using Connect-AzAccount.

Conect Azure SQL database login context

There are chances that your account may be linked to multiple subscriptions. In this case, I have multiple subscriptions associated with my account. You can run the Get-AzSubscription cmdlet to list the subscriptions.

List the azure subscription

List all the geo-redundant backups of the given server

To list all the geo-redundant backups, ensure to set the azure context to the specified subscription using the following cmdlet.

Set the azure context to create Azure SQL databse using Geo-restore backup

The subscription id is the ID output from Get-AzSubscription

Now, you are all set to query the backup details of the specified server using Get-AzSqlDatabaseGeoBackup.

The Get-AzSqlDatabaseGeoBackup cmdlet list all the geo-redundant backups on the given server. In this example, the $resourcegroupname variable declared to store the resource group details and similarly, $servername is for assigning the servername.

Next, these parameters are used to pass it to the Get-AzSqlDatabaeGeoBackup cmdlet to list all the database backup are geo-redundant

Get the property of the Geo-backup

To list the geo-redundant backup specified server and specified database, run the following command

Using the above cmdlet, you could list the geo-redundant backup related to the proddb123456 database. In addition, you can also try to use the filter option to list all the databases using the “*” option. The following command list all geo-redundant backups that start with “proddb”.

Perform Geo-Restore

In this section, you will see the detailed steps to restore the geo-backups. The geo-restore feature comes with the Azure SQL database at zero cost with no extra overhead.

  1. Log in to the portal
  2. Open the CloudShell PowerShell console
  3. List the subscription. If your account has multiple subscriptions, then list the intended subscription where you have the database
  4. Set the subscription Id using Get-AzSubscription output piped to Set-AzContext

  5. Get the geo-backups details into the variable $geobackups for the specified database SSISDB
  6. Prepare the restore by listing the target resource group name, target server and target database and

  7. Perform restore database the the geo backup
1,349 Views