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.
Note: If you buy something from our links, we might earn a commission. See our affiliate disclosure statement.
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 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:
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.
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.
As previously mentioned, based on the service tier, the backup retention period varies. See below:
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:
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:
Comments are closed.