The Costs of Azure SQL Database Backups

Updated April 26th 2016

This post is dated, and I decided to update it with information from Azure SQL Database: Built-in Backups vs Import/Export and from SQL Database FAQ.

Azure SQL Database billing is prorated daily hourly. This means that if you create a database and delete it right away, you are be billed for the full day hour. Each hour is billed at the highest service tier + performance level that was applied during that hour.

I bring this up because the Azure SQL Database automatic backup feature is was built on top of the import/export service. The export follows best practices in order to create a consistent point-in-time copy of your database. This copy is treated as a second database and is billed as such. Then the service exports this copy it to a bacpac file and places it in your Azure Storage.

Today the built-in backup feature is included in the hourly cost of each database. The following is a quick summary of its capabilities.

What are Azure SQL Database Built-in Backups?

Basic, standard, and premium databases are backed up automatically in Azure SQL Database. These backups are retained for 7 days, 14 days, and 35 days respectively. With these backups Azure SQL Database supports two restore capabilities.

• Point in Time Restore allows a database to be restored to any point in time, up to the millisecond, within a database’s retention period.
• Geo-Restore utilizes a database’s latest geo-redundant backup to recover a database. This means a database can be recovered in the event of a regional outage with the 1 hour old geo-redundant backup.

Capability	Basic tier	Standard tier	Premium tier
Point In Time Restore	Any restore point within 7 days	Any restore point within 14 days	Any restore point within 35 days
Geo-Restore	ERT < 12h, RPO < 1h	ERT < 12h, RPO < 1h	ERT < 12h, RPO < 1h
Standard Geo-Replication	not included	ERT < 30s, RPO < 5s	ERT < 30s, RPO < 5s
Active Geo-Replication	not included	not included	ERT < 30s, RPO < 5s

Windows Azure SQL Database Automated Backup Frequency is in UTC (Coordinated Universal Time)

The Windows Azure team has recently released Windows Azure SQL Database Automated Backups. Up until yesterday, I was not sure if the FREQUENCY was in Local Time or in UTC (Coordinated Universal Time). I had a hunch that like the Windows Azure SQL Databases, the FREQUENCY could in fact be in UTC (Coordinated Universal Time).

Today I was informed that it’s not adjusted to the browser’s local time and that we need to set the FREQUENCY using UTC (Coordinated Universal Time).

You can view the current UTC time from http://time.is/UTC

Windows Azure SQL Database Automated Backups are Finally Here!

This weekend, while I was doing a routine systems checkup, I noticed a new feature in on the Windows Azure Management Portal.  The Windows Azure teams have been hard at work and have provided us with the necessary tools to schedule backups of our Windows Azure SQL Databases. As you can observe from the screenshot below, the backup is essentially an export of our database that is stored a Windows Azure Storage Account.

Just in Case Something Goes Horribly Wrong!

Have you ever been asked to modify large amounts of important data? Have you ever made a mistake that requires an embarrassing  amount of man hours to fix?

Well I have! Especially with the Windows Azure Blob Storage Service, where editing blobs couldn’t be easier.

Windows Azure SQL Database has a transaction mechanism that allows us to rollback when something goes wrong. On the other hand, Windows Azure Blob Storage Service does not provide you with transactions. When you overwrite a blob, the previous version is gone… But don’t worry, there are a couple of ways that can help you go about securing a backup.

Before attempting any dangerous data manipulations on the content of your blobs:

• Make a copy on your local machine (This works great when you don’t have much data)
• Copy them to a different Windows Azure Blob Storage Service container (This is a bit drastic, but it works)
• Create a Snapshot for each blob (This is your best bet)

The remainder of this post will focus on exploiting Blob Snapshots in order to provide a means by which we can rollback if something do horribly wrong with our batch processes.

What Does Your Cloud Disaster Recovery Plan Look Like?

I previously wrote about how important lessons usually don’t come easy… and that replication is important. A few weeks ago the Windows Azure platform was going through a rough patch and all SQL Database instances went offline for a few hours.

At that moment, I recommended the use of SQL Data Sync to keep an on-premise backup of your data.

Since then I took a few steps back and read more on the subject. I also setup my first cloud to on-premise SQL Data Sync group and I must say that I’m impressed!

What’s interesting about SQL Data Sync, is that a backup isn’t limited to the cloud. It provides bidirectional synchronization between two or more databases, which can be located on-premise or in datacenters.

Configuring SQL Data Sync doesn’t require any code! Everything is configured through the Windows Azure Management Portal and on your on-premise servers.

There are many different ways to backup your SQL Database instances and they all come at a price. The available options do not cover all the possible disasters on their own so a mix and match may be the solution you are looking for.

The scope of this post will be limited to SQL Data Sync. Future posts will explore other ways to backup your databases. If you are curious about creating an instant copy of your production database within the same SQL Database Server see my posts about creating a staging database instance from a production instance on Windows Azure SQL Database.

Creating a Staging Database Instance From a Production Instance on Windows Azure SQL Database

Debugging and  testing  against a live production database isn’t the greatest idea. At this point in the application’s lifecycle it comes in handy to be able to duplicate the production database to create a temporary staging environment.