Azure SQL Database Resiliency – Point-in-Time Restore

Azure SQL Database provides a number of benefits that leverage resiliency and redundancy built into the underlying cloud infrastructure. You can take advantage of these features in order to perform backup, restore, and failover tasks, which help you recover from human errors, service outages, or even regional disasters. In this article, we will provide an overview of the primary capabilities incorporated into Azure SQL Database, which offers remediation in the first of these scenarios.

In particular, we will focus on point-in-time restore, leaving the coverage of geo-restore and geo-replication options for our future articles. (Note that the latter require the use of Standard or Premium Azure SQL Database, unlike the former, which is available regardless of the pricing tier):

Point-in-time restore relies on automated backups that take place in regular intervals for each Basic, Standard, and Premium Azure SQL Database. More specifically, full backups are taken every week (following the initial full backup that is initiated as soon as a new database is created), differential backups occur daily, and incremental backups are applied every five minutes. Note that the actual occurrence of full and differential backups cannot be exactly predicted, since their timing is dependent on the database utilization.

It is important to realize that performing a point-in-time restore based on automatic backups involve creating a new database on the same server as the original one. This, in turn, implies that the restored instance must be assigned a new name (since it is not possible to have two, identically named databases hosted by the same server). In order to account for the database size limits associated with each service tier, the new database gets assigned the same service tier that was in place at the time when the restore point on which the restore is based was created. The resulting performance level is, on the other hand, determined based on the default defined for the resulting service tier (which is S1 for Standard and P1 for Premium).

If your intention is to provide a fully functional replacement, then once the status and content of the newly restored databases is verified, you would need to delete the existing one, and rename the new one (which can be easily accomplished by running the ALTER DATABASE T-SQL statement while connected to the master database of the server hosting both the original database and its restored version). Alternatively, if you prefer to keep the current version, you have the option of extracting data from the restored database and copying it to the original one.

The restore can be performed by using the Azure Management Portal, Azure Preview Portal (both portals expose the Restore action directly in their respective interfaces), as well as by relying on Azure PowerShell Module (with the Start-AzureSqlDatabaseRestore cmdlet, which generates a restore request that can subsequently be monitored with the Get-AzureSqlDatabaseOperation cmdlet).

The restore functionality also facilitates scenarios where entire databases are unintentionally deleted. However, keep in mind that in such cases, the only restore point available to you is the one immediately preceding the deletion. In addition, you can only restore a deleted database onto the same server where it originally resided. While this is not very surprising, it is worth noting, since it implies that you cannot restore any deleted databases once you delete the corresponding server.

While the point-in-time restore is available in all three pricing tiers, each differs in regard to the retention period. With Azure SQL Database Premium, you have the ability to restore any point within the period of the last 35 days. With the Standard pricing tier, you have access to restore points from the last 14 days. Basic pricing tier limits the duration to the most recent 7 days.

These limits seem to suggest that it might be worthwhile to consider more traditional protection mechanisms based on database copy, export, and import, introduced with SQL Database Web and Business tiers (both of which have already been deprecated), which in addition, allow you to perform restores to a full-fledged instance of a SQL Server. While these methods are still available, the durability and the ease of transition to your on-premises environment (if needed) that they offer should be weighed against their drawbacks. More specifically, they introduce extra cost (associated with storage of BACPAC files), management overhead (unlike automated backups, they necessitate either manual actions or a custom scheduling), as well as inferior recovery time objective and recovery point objective characteristics.

However, if you are still contemplating implementing the export based approach to handling database protection, you would want to ensure that you incorporate additional steps into your custom backup procedure. Such extra steps would involve backing up the source database, resulting in the creation of its replica. While this increases the overall cost of the solution (since you effectively pay for another Azure SQL Database instance), it is required in order to guarantee transactional consistency. This is due to the fact that the .BACPAC file does not contain transaction logs, but instead represents a snapshot of the database content at a point in time. However, once the export from the copy database is complete, you can delete the copy to minimize the cost associated with running an extra Azure SQL Database instance.

This concludes our coverage of the Azure SQL Database recovery options following human errors or data corruption. In our upcoming articles, we will focus on Business Continuity and Disaster Recovery scenarios addressing service or region-wide failures.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles