Last Geo-Backup in Azure Sql DW

February 26, 2018 Training

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 Policy

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:

	try
	{
	# Built-in account
	$servicePrincipalConnection = Get-AutomationConnection -Name "AzureRunAsConnection"
	# Init variables (Automation Account variables)
	$ResourceGroup = Get-AutomationVariable -Name 'ResourceGroup'
	$SqlServerName = Get-AutomationVariable -Name 'SqlServerName'
	$DatabaseName = Get-AutomationVariable -Name 'DatabaseName'

	# Log into Azure with AzureRunAsConnection
	$null = Add-AzureRmAccount -ServicePrincipal `
	-TenantId $servicePrincipalConnection.TenantId `
	-ApplicationId $servicePrincipalConnection.ApplicationId `
	-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

	# Get GeoBackupPolicy State (Enabled?)
	$state = (Get-AzureRmSqlDatabaseGeoBackupPolicy -ResourceGroupName $ResourceGroup `
	-ServerName $SqlServerName `
	-DatabaseName $DatabaseName).State
	$state

	# Get LastAvailableBackupDate
	$lastAvailableBackupDate = (Get-AzureRmSqlDatabaseGeoBackup -ResourceGroupName $ResourceGroup `
	-ServerName $SqlServerName `
	-DatabaseName $DatabaseName).LastAvailableBackupDate
	$lastAvailableBackupDate
	}
	catch {
	throw $_.Exception
	}