Backup options for SQL Azure

SQL Azure makes the move to using Windows Azure easy for many people.  Lots of companies use SQL server and have existing databases and applications in SQL server.  It’s much less daunting moving your SQL server database than re-coding the storage in Windows Azure tables.

One major sticking point with SQL Azure is that there is no native backup.

Microsoft do backup your database on your behalf from time to time but it is not user accessible and Microsoft say that they use this only to restore your data in severe cases of hardware failure.

Realistically nobody is going to run anything other than a tech demo without a backup solution for their SQL database in Azure, so what can be done?

SQL Azure migration Wizard

Back in the early SQL Azure beta days the SQL Azure migration wizard was the only real way to get data in and out of the cloud databases.  Sure you could use raw BCP but the Migration Wizard is built on top of it.

It’s a good tool, especially considering it’s free and seems to have been continually developed.  My main issue was that it was slow, and in older versions I had to copy the entire database every time rather than taking update snapshots.  This leads nicely onto the next backup option.

Red Gate

Red Gate make, amongst other things, excellent SQL server productivity tools.  I had been a fan of SQL Data Compare for years syncing up staging and production databases.  I originally tested their standard SQL Data Compare tool with SQL Azure and it didn’t work, I was initially crushed.

Over a few weeks of emails and some calls expressing how useful their tools would be if SQL Azure was supported Red Gate started a beta program for SQL Data Compare and SQL Compare (schema compare) Sign up to the beta here

SQL Data Compare is my favourite SQL Azure backup tool as it’s very fast.  Rather than copying all the data, you compare the differences between your last local backup SQL database and your SQL Azure database.  The software then snapshots the differences and updates the local database to reflect the changes from the database in the cloud.

There are a whole host of options when comparing the databases, you can select individual tables and columns and even write your own compare where statements to get super granular backups.

Some people may worry about the cost once it comes out of beta but trust me it’s the best tool a DBA can have, it just works, very, very well.

Data Sync for SQL Azure

Data Sync for SQL Azure has been around since November ’09 and it’s pretty much the same as the Migration Wizard.  Its build on top of the Sync Framework 2.0 but it’s not as customisable as SQL Data Compare from RedGate, but it does allow scheduling of synchronisation so feels more like a backup tool.

The best of a bad situation?

I know many people are waiting for the traditional SQL backup / restore interface and the ability to store their SQL Azure backups in the local data centre blob storage.  I guess due to the multi tenancy of the SQL Azure setup this isn’t easy (or they would have done it already, right?)

All three tools are free (or in beta) when this was posted so try all three.  My personal favourite are the Red Gate tools are they are so much more than just a backup tool, but then they are also the most expensive.

This entry was posted in Azure, Backups, Cloud Computing, Microsoft, RedGate, SQL. Bookmark the permalink.

2 Responses to Backup options for SQL Azure

  1. This blog is a bit outdated, but the topic is still really important. Here are a few updates: There is now an import/export functionality available from within the Azure Management portal. It works well, but is limited in functionality. You also have Enzo Backup that comes with a free license for its Standard Edition. And at some point in the future Microsoft will offer a Point in Time restore capability; no firm timeframe on this yet.

  2. Niall says:

    Your right, I have moved away from Azure with work now. I guess I’ll have to trim out some of the very outdated stuff soon.

    I’ll give you that shameless plug for your backup software as Microsoft have dragged their feet for too long to produce a slick backup option for Azure

    Niall

Leave a Reply

Your email address will not be published. Required fields are marked *