DR in Azure Sql DW

Key component of Disaster Recovery Plan for Azure Sql DataWarehouse is a Geo-Backup Policy that must be enabled and working.

Geo-Backup

Azure Sql Datawarehouse uses simple recovery model and special storage level backups (consistent across multiple nodes) are taken every 4-8h. Backup history is stored in sys.pdw_loader_backup_runs DMV. Minimum once a day backup is copied to paired region so RPO is 24h assuming that Geo-Backup actually works. Regarding that - I had an issue with stalled, not refreshing geo-backup and had to raise support ticket. It was escalated to Product Team that fixed it quickly and I got full RCA (increased probability of race conditions related to recent change on Azure side). It has happened before so … trust no one. Untested backup is backup you can’t trust. I’ve prepared small PS runbook to get Geo-Backup Policy status and date of the latest available geo-backup. That’s a good start to end-to-end restore drill.

Gist to extract Azure Sql DW Last Available Geo Backup Date: