Backing Up SQL Azure Databases

Slalom Consultant Joel Forman

Slalom Consultant Joel Forman specializes in cloud computing and the Windows Azure Platform.

One question that I often am asked from clients is around how to back up SQL Azure databases. In this blog post I will outline some of the capabilities and offerings available in this space.

The first place I start when answering this question is to clarify what the platform provides for you and what it does not. Every SQL Azure database is replicated multiple times for redundancy and recover-ability. This is outstanding in terms of availability and disaster recovery. It means that my application is insulated from a hardware failure on a SQL Server. If there were a problem with my “master”, I may not even know if the platform switches me over to a “slave”.  But I do not have access to those replicas. And unfortunately, “point-in-time” copies of my database are not created nor made available to me by default.

A lot of customers have a backup process in place for different reasons. Some take nightly incremental backups of their data for safe keeping. This could insulate them in the case where data was corrupted. Others take backups before major code releases or revisions for rollback capabilities. In these cases, the platform does not provide me with backups of my database automatically, but it does provide different tools to create them.

Creating a Copy
There is a T-SQL command that you can run against your SQL Azure master database to create a point-in-time copy of a SQL Azure database to a new SQL Azure database. Here is an example of that command:

CREATE DATABASE [MyDatabase_Copy1] AS COPY OF [MyDatabase]
GO

This is a very useful tool for a variety of scenarios. I have seen this used before major releases for a rollback plan. It is also a good tool to use for developer’s trying to replicate a bug or problem against a copy of a database where the issue exists. One thing to remember when making copies is that you are being billed for each copy that you create. Remember to remove them when you are done.

I have seen some automation put in place that runs this command on a nightly basis to create a nightly backup process. This also contained logic to only keep X number of copies on hand and remove the oldest. This is a viable solution, but there is the associated costs of keeping the copies in the cloud.

SQL Azure Import/Export
The ability to import and export SQL Azure databases via the Data-tier application framework now exists in all Windows Azure data centers. Not only is this a great way to migrate databases to SQL Azure, it is also a great way to back up data from SQL Azure. In the backup scenario, a BACPAC file is created, containing schema and data from the database, and exported as a BLOB to a Windows Azure Storage account. Since BLOB storage is inexpensive (currently $0.14 /GB/Month), this is also a very cost efficient backup solution.

The Windows Azure Management Portal allows you to export a SQL Azure database in this fashion with only a few clicks. Follow these easy steps:

1. Sign in to the portal at http://windows.azure.com.
2. Click on “Database” in the lower left navigation.
3. Expand your subscription and click on your SQL Azure server.
4. Highlight the SQL Azure database you wish to export.
5. At the top ribbon, click on “Export”.

Slalom Consulting - Backing Up SQL Azure Databases
6. In the dialog window, supply the credentials to your SQL Azure server and supply the BLOB name and storage credentials for target storage account where the exported BACPAC file will be saved.

Slalom Consulting - Backing Up SQL Azure Databases
7. Your export job will be started, and you should see the BACPAC file appear shortly at your target location.  You can check up on the status of the export job by clicking the “Status” button in the top ribbon.

You can import BACPAC files to new SQL Azure databases via the portal as well, following a similar process.

There is a CodePlex site containing SQL DAC examples at http://sqldacexamples.codeplex.com. Here you can find the downloads for the DAC framework dependencies as well as examples on how to use some of the tools. From here, it is easy to learn how to import BACPAC files from SQL Azure to local on-premise SQL Servers. You can also easily see how a backup process could be automated via these tools and commands.

There is a third-party tool, SQL Azure Backup by RedGate, that offers backup services for SQL Azure. There is a free download if you are interested in checking it out.

In summary, while implementing a backup strategy left up to you, there are several tools available that allow you to do so.

Slalom Consulting’s Seattle office Slalom Consulting's Project & Cloud focus
Learn more about our Seattle office Learn more about Slalom Consulting Cloud

subscribe by emailSubscribe to follow new Cloud posts

About Joel Forman
Joel Forman is a Solution Architect at Slalom Consulting and specializes in cloud computing and the Windows Azure Platform.

One Response to Backing Up SQL Azure Databases

  1. Luiz Inacio says:

    Thanks for the blog! It really helped me…
    One thing I don’t like about the import functionality is that I can’t re-import a bacpac file to the same database. Even if I delete and recreate the database, the import will fail…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: