One of the most important PaaS capabilities that Azure SQL Managed Instance provides is an ability to get your databases up and running even if entire data-center where you have placed your instance and databases is down or unavailable. There are two approaches that you can use to ensure that your databases can be recovered even if the entire data center is down:
- Geo-replication using auto-failover groups that continuously copies the changes made on all databases placed in your Managed Instance and apply them on the matching databases on a separate instance in the another region. This technology enables you to failover your instance to another region.
- Geo-Restore that enables you to take your database from a region even if the entire region is down, and restore it to another instance in different geo-region that is available.
These features are important in so-called geo-disaster recovery (GeoDR) scenario where the entire region can be down due to some disaster, and you still need to recover your database that was placed in the affected region, without need to wait for a region to be available again.
Geo-Replication enables you to quickly recover your databases since remote copy of the instance is continuously getting the changes from primary; however, you need to pay for this additional instance. Geo-Restore is slower method because the time needed to recover your databases depends on the data size; however, you don’t need to always keep two instances. This is a trade-off between cost and speed of recovery.
In this article you will see how to restore your database to another region using PowerShell commands.
Geo-Restore using PowerShell
Azure SQL Instance provides PowerShell modules that enable you to perform geo-restore of your database. You can use Restore-AzSqlInstanceDatabase command to take a backup of a database on the instance in one region and restore this backup to the instance in another region.
First you need to install Az.Sql PowerShell module:
Install-Module -Name Az.Sql
Then you need to connect to your Azure account and select the subscription where your database is placed:
Connect-AzAccountSet-AzContext -SubscriptionId "me7b137-06c0-c9a924-9788-0969e14bde"
Now you need to get the backup from the source instance in one region using Get-AzSqlInstanceDatabaseGeoBackup command:
$backup = Get-AzSqlInstanceDatabaseGeoBackup `-ResourceGroupName "source_rg" `-InstanceName "sourceinstance" `-Name "MyDatabase"
Finally, you should use Geo-restore to restore that backup to the instance in another region (note that the instance must be in the same subscription as the original one):
$backup | Restore-AzSqlInstanceDatabase -FromGeoBackup `-TargetInstanceDatabaseName "MyDatabase"`-TargetInstanceName "targetinstance" `-TargetResourceGroupName "destination_rg"
As a result of these commands you will get the backup of the database from another region, and restore it on another one.