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?
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 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 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.