Useful tools for SQL Azure deployment and backup

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.

Deployments

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.

Solution

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.

Backups

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?

Solution

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:

  1. It’s free Sorriso
  2. 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.

Advertisements

Published by

ricardofiel

CTO @ Rupeal. Guitar player @ David Fonseca. Sharing my own stuff on SoundCloud and twitter as theplastictoy.

3 thoughts on “Useful tools for SQL Azure deployment and backup”

  1. Great post,

    As a side note I use Redgate’s SQL Azure Backup quite regularly to backup cloud databases to an onsite premise and thought it worth noting that if for any reason the backup fails then do check that a “CopyOf[DatabaseName]” database isn’t left on your SQL Azure server. Redgate’s application creates a “CopyOf[DatabaseName]” during the backup process and won’t tell you that it’s been left there if a failure occurs.

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