I was a guest speaker yesterday at Microsoft during a Windows Azure training. We’ve been doing some medium-scale work in Azure and I was there, together with create|it’s CTO João Pedro Martins, to talk about our real world experiences with Windows Azure.
One of the topics was SQL deployment and backups. Although Microsoft has worked hard to ensure that SQL Azure is the closest possible to SQL Server, there’s a couple of things you should be aware of. There’s a good comparison chart at http://social.technet.microsoft.com/wiki/contents/articles/996.compare-sql-server-with-sql-azure.aspx. I won’t discuss the differences. Instead, I’ll just share some tools and practices we’ve been using, specifically for deployment and backups.
For most of our on-premises database sync work we use RedGate tools, mostly SQL Compare and SQL Data Compare. We’ve had good experiences with these tools on SQL Azure, but for BIG databases, they may not be the best solution. There’s a good description of some problems you may encounter at SQL Azure Connection Management. Mostly, for BIG databases, we’ve experienced more than once connection timeouts.
An excellent set of tools for large database deployment is SQL Azure Migration Wizard, which is free and open-source, hosted at codeplex. Inside the downloads area of the project, you’ll find SQLAzureMWBatchBackup and SQLAzureMWBatchUpload, which are command line tools and can automate your SQL deployments. Read the documentation of these projects for all the details.
You’re using “the cloud” and you should not worry about backups, right? Wrong! Although Microsoft ensures that your data is not lost, Microsoft can’t protect you from the most common source of data loss: human error.
Sometimes we have weekly or more deployments of a website hosted on Azure. Even if don’t have this process automated, are you really going to remember backing up your SQL Azure database before deployment? Every time? Every single time?
We’ve been using the SQL Azure Backup tool from RedGate and loving it. It has been discontinued as RedGate now provides a service for this (RedGate Cloud Services). However, the things I love about SQL Azure Backup are:
- It’s free
- It has both a GUI and a command-line, which means YOU CAN AUTOMATE YOUR SQL DEPLOYMENTS
Our current set-up is, for each project, we host at one of our on-premise servers, a batch file that calls the command line version of SQL Azure Backup once a day. We’ve been using this for quite some time now and never had a problem.
This creates a bacpac file (a very compressed and human readable format) with all data and schema from your database and stores it in your Azure Storage account. Given the cheap price of storage, don’t worry about keeping backups in there.
DAC exports are not transactionally consistent, so if you want to ensure this, you can use the /CreateCopy switch of SQL Azure Backup. This creates a transactionally consistent copy of you database and uses that database for the bacpac generation.
Just a quick note: SQL Azure Backup calls an Azure service called SQL DAC Import/Export, so this is an async process. Your backup may not be performed immediately, but we’ve noticed that it never exceed the 1 minute mark. SQL Azure Backup polls the DAC service, so it only terminates once the backup is complete.