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:
- Azure SQL Database business continuity plan and disaster recovery solution
- Definitions of RPO and RTO
- Discuss Azure SQL database automatic database backup
- Explain Geo-restore with examples
- 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.
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 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.
|
1 |
PS:/home/pjayaram> Set-AzContext <subscriptionID> |
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
|
1 2 3 4 5 6 |
$resourceGroupName= 'sqldba-scus-01-rg' $ServerName=' azuresqldemo12345' #List the Geo-redundant backup $GeoBackups = Get-AzSqlDatabaseGeoBackup -ResourceGroupName $resourceGroupName -ServerName $ServerName #To list the property of the geo-redundant backup, run the following command. $GeoBackups|Get-Member |
To list the geo-redundant backup specified server and specified database, run the following command
|
1 |
PS home/pjayaram\>Get-AzSqlDatabaseGeoBackup -ResourceGroupName "sql-01-rg" -ServerName "azuresqldemo12345" -DatabaseName "proddb123456" |
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”.
|
1 |
PS home/pjayaram\>Get-AzSqlDatabaseGeoBackup -ResourceGroupName "sql-01-rg" -ServerName "azuresqldemo12345" -DatabaseName "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.
- Log in to the portal
- Open the CloudShell PowerShell console
- List the subscription. If your account has multiple subscriptions, then list the intended subscription where you have the database
-
Set the subscription Id using Get-AzSubscription output piped to Set-AzContext
1Set-AzContext -SubscriptionId $subscriptionId - Get the geo-backups details into the variable $geobackups for the specified database SSISDB
-
Prepare the restore by listing the target resource group name, target server and target database and
1Set-AzContext -SubscriptionId $subscriptionId - Perform restore database the the geo backup
- Note: To perform geo-restore use -FromGeoBackup. In addition, the other important parameter is ResourceId
- Log in to the Azure portal
- Browse the Azure SQL Server
- Create a SQL database
- Select a backup
- Browse the database
- Click OK
- Note: You can only see percent_complete progress as 0%, 50% or 100%
- Note: The geo-restore may take a little longer to restore when compared to other DB cloning operations
- You may need to change the connection string or setting to point to the applications to the new server
- You may need to add the express route IPs or client IPs to the firewall rules so that the application can connect to the new server. In addition, you need to check the database-level firewall and get it added, if any
- Create the logins and apply the permissions, if any
- Enable auditing, if required
- Enable alerts, if required
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
In this case, the SSISDB restored to the target Azure SQL database.
|
1 2 3 4 5 6 7 8 9 |
$resourceGroupName= 'sqldb-scus-01-rg' $ServerName='sqlnonprod01' $TargetResourceGroup='sqldba-scus-02-rg' $TargetServer='azuresqldemo12345' $TargetDatabaseName='SSISDB_Target' $GeoBackups = Get-AzSqlDatabaseGeoBackup -ResourceGroupName $resourceGroupName -ServerName $ServerName $GeoBackups | Where-Object {$_.DatabaseName -eq "SSISDB"} ($GeoBackups | Where-Object {$_.DatabaseName -eq "SSISDB"}).ResourceId Restore-AzSqlDatabase -FromGeoBackup -ResourceGroupName $TargetResourceGroup -ServerName $TargetServer -TargetDatabaseName $TargetDatabaseName -ResourceId ($GeoBackups | Where-Object {$_.DatabaseName -eq "SSISDB"}).ResourceId |
Perform a geo-restore operation using Azure Portal. You can also perform the geo-restore operation using the Azure Portal.
How to measure Geo-restore progress
Detailed restore progress is not available on the portal. I feel it is an important feature and Microsoft will consider update in the future release.
However, you can query the sys.dm_operation_status DMV to measure the progress of the restore. You can run the following T-SQL to get the progress.
|
1 2 |
SELECT session_activity_id, operation,state, state_desc, percent_complete,last_modify_time, * FROM sys.dm_operation_status WHERE operation LIKE '%DATABASE RESTORE%' |
Perform failover tasks
After the failover or recovery, you may require performing the following tasks before confirming the users about applications are up-and-running and available for use:
Summary
In the cloud journey, IT organizations have realized that the BCP (Business Continuity Plans) and DR (Disaster Recovery) are so important, and it’s never ever been a case to design an effective plan to withstand the major outages—this may be the area of—protect the sensitive-and-critical data; threat detection and management; how to prevent ransomware attacks; cyber-attacks; GDPR compliance, etc., While defining and planning the standards to safeguard the data, backup protection and disaster recovery technique in meeting RPO (Recovery Point Objective) and RTO (Recovery Time Objective) is utmost important. With the aforementioned points, there’s been no better time to understand the implication and review-and-realign the IT resilience program that meets the BCP objective.
Azure SQL database with the geo-restore feature may not be a suitable candidate for BCP or DR solution where the application requires shorter requires time and application availability is at high-stake. Geo-restore doesn’t permit Point-In-Time-Restore (PITR) and recovery is subjected to RTO up to 12 hours and RPO is up to 1 hour. It is of utmost importance to take measures that will help to ensure business availability based on criticality.
That’s all for now…
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram