Click here to monitor SSC

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

Cloud Backup: Getting the Users’ Backs Up

Published 24 June 2011 2:40 pm

On Wednesday last week, Microsoft announced that as of July 1, all data transfers into its Microsoft Azure cloud will be free (though you have to pay for transferring data out). On Thursday last week, SQL Azure in Western Europe went down. It was a relatively short outage, but since SQL Azure currently provides no easy way to take a standard backup of a database and store it locally, many people had no recourse but to wait patiently for their cloud-based app to resume. It seems that Microsoft are very keen encourage developers to move their data onto their cloud, but are developers ready to do it, given that such basic backup capabilities are lacking?

Recently on Simple-Talk, Mike Mooney described a perfect use case for the Microsoft Cloud. They had a simple web-based application with a SQL Server backend; they could move the application to Windows Azure, and the data into SQL Azure and in the process free themselves from much of the hassle surrounding management and scaling of the hardware, network and so on. It was a great fit and yet it nearly didn’t happen; lack of support for the BACKUP command almost proved a show-stopper. Of course, backups of Azure databases are always and have always been taken automatically, for disaster recovery purposes, but these are strictly on-cloud copies and as of now it is not possible to use them to them to restore a database to a particular point in time.

It seems that none of those clever Microsoft people managed to predict the need to perform basic backups of Azure databases so that copies could be stored locally, outside the Azure universe. At the very least, as Mike points out, performing a local backup before a new deployment is more or less mandatory.

Microsoft did at least note the sound of gnashing teeth and, as a stop-gap measure, offered SQL Azure Database Copy which basically allows you to create an online clone of your database, but this doesn’t allow for storing local archives of the data. To that end MS has provided SQL Azure Import/Export, to package up and export a database and its data, using BACPACs. These BACPACs do not guarantee transactional consistency; for example, if a child table is modified after the parent is copied, then the copied database will be in inconsistent state (meaning, to add to the fun, BACPACs need to be created from a database copy). In any event, widespread problems with BACPAC’s evil cousin, the DACPAC have been well-documented, and it seems likely that many will also give BACPAC the bum’s rush.

Finally, in a TechEd 2011 presentation tagged "SQL Azure Advanced Administration", it was announced that "backup and restore" were coming in the next SQL Azure CTP. And yet this still doesn’t mean that we’ll get simple backups as DBAs know and love them. What it does mean, at least, is the ability to restore any given database to a point in time within a 2-week window.

For the time being, if you want a local copy of your data and don’t want to brave the BACPAC, one is left with SSIS or BCP, creative use of schema and data comparison tools, or use of SQL Azure Backup (currently in beta) in order to perform this simple but vital task.

Cheers,

Tony.

4 Responses to “Cloud Backup: Getting the Users’ Backs Up”

  1. Keith Rowley says:

    I can see using SQL Azure for a relatively static database, or for an actively changing one where the data is not mission critical such as logging visitors to a website.

    At the moment I would not even consider using a cloud based database without local backup options for a mission critical application. My job means too much to me for that.

  2. BuggyFunBunny says:

    – My job means too much to me for that.

    The problem with Suits, alas, is that many decide they know more than the “experts” (never trust anyone who’s spent years honing his art, they’re just Damned Elites), and mandate such folly. When it blows up, as is inevitable, the Suits blame the Worker Bees for not doing a Good Job.

    Such situations can not be mitigated, only abandoned for safer passage. As with the Housing Bubble (everybody’s doing it, so it must be OK, and anyway, if we don’t we’ll be Left Behind) which caused the Great Recession , Suits also tend to Lemming Think (second cousin, once removed to Emperor’s New Clothes Think), thus we can expect many Cloud Deployments, followed by at least equally many Cloud Disasters.

    In sum, for most of us, we don’t get to make the decisions which have the greatest impact on our ability to Do Good Work. We’re just mindless piece workers.

  3. SergioE says:

    I can’t really think in a company trusting only in the cloud to have the more important thing “their information”, this is the most valuable resource in any enterprise and the idea of not have a local copy of this data is unimaginable.

    Best regards,
    Sergio E.

    — original en español —
    Realmente no puedo pensar en una empresa confiando sólo en la nube lo más importante “su información”, ésta es el recurso más valioso de cualquier empresa y la idea de no tener una copia local de estos datos es inimaginable.

    Saludos,
    Sergio E.

  4. jerryhung says:

    It’s surprising the bread & butter of any DBA – BACKUP & RESTORE is not part of Azure from the start. What good is cloud then

    Also, does Azure offer TAPE retrieval for archived data? e.g. normally I’d go to tape for anything older than 6 months or 1 year. It takes a while but one can get it
    How does it work for Azure if we cannot even get a local backup of the database

    Personally not a fan of Cloud, but I do love Dropbox.. and my local copy IS my Backup (and I can backup/compress it myself)!

    Jerry

Leave a Reply