Blog & Company Updates

Disaster Recovery in Azure SQL Data Warehouse

What is your disaster recovery plan for your Azure SQL Data Warehouse? Today I’d like to talk about disaster recovery in Azure SQL Data Warehouse, more specifically, about a feature that was released with Azure SQL Data Warehouse Gen2 called Geo-backup policy.

The image below shows my configuration of an Azure SQL Data Warehouse. I have a menu shown here for the Geo-backup policy and you’ll see my selection says it’s enabled but I don’t have the choice of turning it on and off. In Gen2 this is turned on by default and cannot be turned off.

DisasterRecoveryAzureSQLDWGen2_02

If you’re using Gen1, although it is turned on by default so the Geo-backup policy is enabled automatically, you can opt out of this feature unlike Gen2.

What Geo-backup policy does is it automatically creates backups of our Azure SQL Data Warehouse to store in a different region or in a region other than where our data warehouse is hosted. This means my backups are automatically taken and stored in the other region in a region pair.

Microsoft has created these region pairings so if I have a data warehouse hosted, for example, in East US, then the Geo-backups will be taken and stored in the West US. A partial list of the region pairings is below; you can view the full list on Microsoft’s website.

DisasterRecoveryAzureSQLDWGen2_03

The regions in a pair are generally more than 300 miles apart from each other with the idea being that any outage impacting your home region should not impact the Geo-backup region. A few more interesting points about Geo-backups:

    • Geo-backups are performed only once per day, so don’t get this confused with backups that are happening on the original or local copy. Those are still happening via snapshots more frequently throughout the day.
    • If I must do a restore, I can restore that Geo-backup anywhere I want to any other region that supports the Azure SQL Data Warehouse product. So, I’m not limited to restoring it to the paired region that Microsoft has determined for me.
    • The solution with these backups is not a high availability or an automatic fail over type solution. It’s much different than some of the features we have available for redundancy and high availability with Azure SQL Database. This is not any type of synchronous replication and there’s no automatic failover to point to the backup data center – it’s strictly a backup process.

The Geo-backup policy as part of Azure SQL Data Warehouse Gen2 is an interesting feature and can be very helpful.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

 

Author

  • Bob has broad experience designing and implementing Business Intelligence and Data Warehouse solutions on the Azure and Microsoft SQL Server platforms. He has spoken at SQL Saturdays and has presented webinars on various cloud architecture and data management topics.

Bob RubockiDisaster Recovery in Azure SQL Data Warehouse