How to Backup your Azure SQL Database

I have already spoken about High Availability for SQL Server deployed in an Azure VM. For SQL Server deployments in Azure VM’s (IAAS), you are entirely responsible for its availability and business continuity planning. However, in case of Azure platform as a service (PAAS), Microsoft provides cloud a lot of these features as a part of the Azure SQL PAAS package. In this post, I am going to talk about how to backup your Azure SQL Database.

How to Backup your Azure SQL Database

Microsoft SQL Database on Azure provides you recovery options for both planned and unplanned outages. SQL Database on Azure PAAS has backed up automatically, and you are not responsible for backing it up yourselves.

Azure SQL Database is backed up Automatically

The business continuity features of SQL Database as-a-service is broadly categorized into three buckets:

• Point in Time Restore

Point in time restoration process is similar to T-Log (Transaction Log) backup restoration process. You can restore your data to a point in time, based on the backup that is available. The backup retention period is not user-driven. Instead, it is dependent upon the service tier that you are in.

Note: You can only restore a Azure SQL Database with a different name

See image below:

• Geo – Restore

Supported on all service tiers, i.e., Basic, Standard and Premium, the Geo – Restore feature allows you to restore your databases from a different region, other than the primary region, thereby providing site resiliency. This feature is exactly like the Point-in-time restore feature with one minor difference. In this case, the database is restored from a backup chain stored in a geographically-replicated Azure blob storage (RA – GRS). The Azure service with maintaining a backup chain with a frequency of a full weekly backup, multiple times a day-differential backup, and T-Log (transaction logs) saved every 5 minutes.

Please note that there can be a delay of up-to 1 hour between backups across the replicated Blobs in RA-GRS.  RPO = Up to 1 hour

The Geo-restoration time depends upon several factors like network bandwidth utilization, database size, the number of T-Log’s, etc. The time taken is determined by the service and is not user controlled.

Source: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-geo-restore/

• Active – Geo-Replication

Microsoft uses the SQL Server Always on availability groups behind the scenes to provide you this feature. Unlike the On-Premises or the IAAS setup, in this case, the user is only responsible for selecting the region they want the secondary server to be. With just a few clicks, you can have an active secondary. You can have up to 4 active secondaries, and they can be either Readable or Non-readable.

Here are the key points to summarize this offering:

• Up-to 4 readable secondary replicas.
• Asynchronous replication only. The secondary replicas will have data lags.
• Only committed transactions are replicated over to the secondaries.
• Automatic fail-over is provided out of the box.
• You can also manually failover using Power-shell scripts or using the Azure management (new) portal.
• All Databases are transactionally consistent.
• All secondary non-readable databases would be migrated to a readable secondaries post-April 2017.
• DDL transactions (Schema-changes) will take more time to be committed on the secondary server(s).
• You can have a mix of service tiers with Primary and secondaries.
• Your application can off-load read-only queries to the secondary server.
• The decision to go for Geo – Replication will depend on how much mission critical your application is.
• Additional cost needs to be taken into consideration.

As previously mentioned, based on the service tier, the backup retention period varies. See below:

Source: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-business-continuity

Azure SQL Database Copy Option

This allows you to create a copy-only database backup, that can be imported to create a different Azure SQL Database with a different name. See image below:

Azure SQL Database Export Option

Just like Copy-Only backups, you can also export your database directly to a Blob storage container. See picture below:

To summarize, Azure SQL Database provides a comprehensive set of options to its users for business continuity. To learn more about these features visit the official Microsoft site for detailed documentation:

• Cloud business continuity and database disaster recovery with Azure SQL Database
• SQL Database Active Geo-Replication