Recovering an Azure SQL Database During a Region Outage

Today we are going to discuss what to do if the Azure region in which you host your Azure SQL Database suddenly becomes unavailable. Many automatically start thinking about active geo-replication and leveraging fail-over groups for your Azure SQL Database. That approach is an excellent one to take if your need or budget fits. However, if you aren’t using geo-replication and fail-over groups, then you can always rely on the backups provided by Azure.

Azure provides point-in-time restores of your Azure SQL Database and, as a bonus, also offers automated backups that leverage read-access, geo-redundant storage (RA-GRS) which is available on all tiers. RA-GRS will leverage the paired Azure region to store the read-only backups. A list of paired regions can be found here. This may end up being the desired strategy for those systems that are not critical and can tolerate a little downtime.

We now have the basic information necessary to restore your Azure SQL Database. We’re going to walk through this example leveraging PowerShell. The first step is to log into Azure.

Connect-AzAccount
Set-AzContext -SubscriptionObject $(Get-AzSubscription -SubscriptionName MySub)

Now we can retrieve our last geo-backup that is available for your target database.

$backup = @{
  ServerName    = "mydemo-dev-eus-ss"
  DatabaseName  = "mydemo-dev-db"
  ResourceGroup = "mydemo-dev-eus-rg"
}

$geoBackup = Get-AzSqlDatabaseGeoBackup $backup

Now let’s create a new database from this backup.

$restore = @{
  ResourceGroupName  = "mydemo-dev-wus-rg"
  ServerName         = "mydemo-dev-wus-ss"
  TargetDatabaseName = "mydemo-dev-deb"
  ResourceId         = $geoBackup.ResourceID
}

Restore-AzSqlDatabase -FromGeoBackup $restore

That’s it. You should now have your database restored from the most recent geo-backup on the SQL Server in the new Azure region. Here is the script in its entirety.

Connect-AzAccount
Set-AzContext -SubscriptionObject $(Get-AzSubscription -SubscriptionName MySub)

$backup = @{
  ServerName    = "mydemo-dev-eus-ss"
  DatabaseName  = "mydemo-dev-db"
  ResourceGroup = "mydemo-dev-eus-rg"
}

$geoBackup = Get-AzSqlDatabaseGeoBackup $backup

$restore = @{
  ResourceGroupName  = "mydemo-dev-wus-rg"
  ServerName         = "mydemo-dev-wus-ss"
  TargetDatabaseName = "mydemo-dev-deb"
  ResourceId         = $geoBackup.ResourceID
}

Restore-AzSqlDatabase -FromGeoBackup $restore

Next Step—Be Prepared

I recommend taking this script and stick in your disaster recovery playbook so you’ll have it in case you need it. One last item to keep in mind is the restoration time of this method. Database size, target region load, and network bandwidth will factor into your restoration time.

Jamie Phillips

Jamie (@phillipsj73) is a Senior Cloud Engineer at SentryOne and working remotely in East Tennessee. He has been working with .NET since 2007 after discovering .NET development in graduate school. His Geology degree has given him an appreciation for large systems and processes which has created keen interest in Solutions Architecture, DevOps, and the Cloud. During the day he work on Windows, but at night he is all about Linux.