Last week in Azure SQL Database – Part 2 – New preview services

Note: Well that was quick. I’ve updated this blog entry (same day) to reflect clarifications provided by a member of the Azure SQL Database team. Thanks for these excellent clarifications. For now (I may go back and change this later) changes from the original blog post are indicated with italics.

The last post in this series was about the new tiers/pricing in Azure SQL Database (ASD). This post will be more exiting, as it covers the new services that come with the new tiers. I’m talking about what the announcement (and docs) call “Business continuity features”. To summarize these features are Self-Service Restore and Disaster Recovery – Geo-Replication.

Although the docs and the chart on show these features as available on all new tiers, currently, these services only appear on premium. And the PowerShell cmdlets mentioned in the docs aren’t in Azure PowerShell 2.3. I was told the cmdlets “will be out this(Apr 28) week”. See the chart for how the new features are implemented on different tiers.

One final thing about using “CREATE DATABASE .. AS COPY OF” and the preview. Last year’s Premium preview created a copy that was in a “disabled premium” state. The new preview will create a copy at the same level, so, for example, “CREATE DATABASE .. AS COPY OF” with a P2 database will create a P2 database. This has charge repercussions.

First, Self-Service Restore. Microsoft keeps (and has always kept) database backups at their data center. I’m guessing these are “traditional” database backups (not BACPACs). BUT, you can’t use their backups, because Backup and Restore are not supported on ASD. Export and Import are supported. Self-service restore is a way you can have them use THEIR backups to restore an ASD database. There are two flavors of self-service restore:
1. Restore a copy of a currently existing database as of a point-in-time. Perhaps you deleted a table, for example, or some data with a miscoded SQL DELETE statement. It happens…
2. Restore a copy of a database you deleted by mistake. Or that you want back. The database doesn’t currently exist now.

I’ve heard both of these referred to as “oops recovery”. I’m thrilled with this service, even though you and I have never made a mistake, right? 😉

Use the portal (see or the PowerShell cmdlets Start-AzureSqlDatabaseRestore (for a Standard or Premium Edition database) or Start-AzureSqlDatabaseRecovery (for a Basic database, because it doesn’t have point-in-time recovery), and a restore request will be submitted for you. To restore a deleted Standard or Premium database, just restore to a point-in-time before you deleted it. There is no SLA on *how long* the request will take to process. I couldn’t even get a ballpark figure, because it depends on the size of the database and the amount of recent activity. You can, however, get information about the status of the restore operation. You can even get this a T-SQL with the sys.dm_operation_status metadata table.

Unlike “CREATE DATABASE .. AS COPY OF”, self-service restoring a database produces a database of the same tier, but the lowest performance level in that tier. For example, restoring a P3 database creates a P1 database as a copy. This lessens the charge repercussions, but you do, of course, pay for at least one day of the copy.  If you’re manually just using it to recover a table, don’t forget to delete the restored copy when you are done. The database that’s created with a restore request can have the same name or a different name as the original and is always created on the same logical server, same data center. So to use a restored copy, you’ll need to change connection strings to point to the new database name or choose the same name when you when you submit the restore request. You may also want to increase the performance level, if you want to use the copy in place of the original afterwards.

If you’re the kind of person who wants their own backup (to Import the data on-premises, for example) you’ll still need to use Export/Import and BACPACs. The backup/restore capability is not available to you. BTW, if you used the Automated Export service (in preview itself) with Web/Business (it produces BACPACs to Azure Storage on a schedule), this is NOT available currently on the new tiers (at least that I could see, on the portal). No announcement when/if it will be.

To reiterate, the level of self-service restore (length of retained backups and point-in-time or not) is dependent on the service tier. See the chart referred to above. Also, here’s a clarification of what “Most recent daily DB backup in past 24 hours” means for Basic tier. For each database the service manages several types of backups: full backup created once a week, differential backup created once a day and transaction log backup created every 5 minutes. The first two are also copied to the Azure storage and that is what we refer to as “daily backups”. The actual time those backups are created differ therefore we can only guarantee that they will not be older than 24 hrs.  Consequently, if a database is recovered using Start-AzureSqlDatabaseRecovery the data loss (RPO) will be less than 24 hrs.

As this post is getting too long, I’ll save disaster recovery – geo-replication for another post.

Cheers. @bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.