How to backup Azure databases

As we start using SQL Azure more and more for storing data, we had to come up with a easy and inexpensive way to backup Azure databases. There is a number of various tools available to backup Azure databases, but they usually require a separate install and they are never free. Although, sometimes, they are fairly inexpensive. I like free ones better though.

So, after a bit of research I have discovered an easy way to backup SQL Azure databases to my on-premise (offsite) SQL Server: SQL Data Sync. D’oh! This is an existing functionality in Azure, and it can be accessed through an “old” Windows Azure portal interface ( I am not going to write step-by-step instructions because, in this case, user interface is actually very intuitive and once you get to Data Sync part of Azure portal, you will know what to do. Good luck!

{ Ping me, if you need any help or have any questions about this article }

AzureFest: Open Source Presentation

Undoubtedly by now you have heard of AzureFest, with any luck you have been out to one of the events [if you live in the GTA]. For the rest of you, that haven’t been able to experience the event, I wanted to take the opportunity to introduce you to what AzureFest is and why you might be interested in the event itself.

Windows Azure Data Center Locations

What is AzureFest?

At it’s core AzureFest is a talk that focuses on a few barriers to Windows Azure Adoption including Pricing, Registration, Platform Confusion and Coding/Deployment. This is not your Grandma’s Windows Azure Presentation, It includes both a lecture and a hands on component which is rare for a Community Event.

Why Talk Pricing?

Simple, pricing is the first question that I get asked at the end of every presentation that I’ve done to date, so why not talk about it first?  Pricing goes hand-in-hand with the Platform, which means not only do you get to understand what the Windows Azure Platform consists of, but you also get an understanding of what it will cost as well. Finally, It would be rather irresponsible not to talk about the costs of Windows Azure when the first Hands-on-Lab is a walkthrough of the registration process.

What Will I Learn?

Besides the Overview of the Platform and the Pricing Strategies, each attendee who participates in the Labs will learn:

  • How to Register for a Windows Azure Platform Subscription
  • How to Create, Manage, Configure and Leverage a SQL Azure Database
  • How to Create and Configure a Windows Azure Storage Service Account
  • How to Create & Deploy a Project to Windows Azure Compute

Attendees will also learn some of the gotcha’s around the Tool Installation/Configuration Process and some strategies on how to debug your cloud based solutions both on premise [using the Compute Emulator] and “In The Cloud”.

Windows Azure CDN Locations

Bonus… We’re giving it away!

In the spirit of growing adoption of the Windows Azure Platform within Canada [or any country for that matter], ObjectSharp is releasing the content as an Open Source Presentation. This means it is FREE for anyone to download, learn and/or deliver.

If you are interested in doing an AzureFest presentation in your area, download the Resources for AzureFest. The resources include:

  • An AzureFest Slide Deck
  • Hands-on-Lab Kit [Ready to deploy cspkg and cscfg files]
  • Modified NerdDinner Source Code for Hands-on-Lab

If you have specific questions about delivering an AzureFest presentation, or need clarification on the content, please direct your questions to me via twitter.

Windows Azure Troubleshooting Glossary

One of the hardest things we face as developers is Troubleshooting and Debugging code in different environments and scenarios, this continues to hold true in the Cloud. This Blog post will outline some of the resources available to developers that are trying to troubleshoot Windows Azure.

Developers, Developers, Developers... Code with Microsoft

Troubleshooting the Environment in Windows Azure

Troubleshooting Windows Azure Operating System Issues

Even though the Cloud attempts to limit diversity amongst it’s hardware, the Operating System is something that will always need to be able to adapt new features or emerging security threats.

One thing that Microsoft has done particularly well is keeping Operating System upgrades very Abstract in Windows Azure by releasing a new Virtual Machine (VM) Image (OS Guest) with every set of new upgrades. The VM images are controlled in the Cloud Service Configuration (cscfg) file by setting the osFamily and osVersion attributes.

OS Guest Resources

VM Role Resources

Troubleshooting Windows Azure Deployment

Deployment is the stage of development in which you have the least amount of control. A number of Debugging paradigms are not available unless the Role Initializes and is created successfully. Once the Role is created, you will be able to debug using Remote Desktop Access to Windows Azure (if configured), or Download Intellitrace Diagnostics Information (if enabled).

With the introduction of Start-Up Tasks, many new scenarios that may involve debugging have been introduced. Be sure to test your startup scripts using RDP before trying to deploy your application with the Tasks configured in the Cloud Service Definition (csdef) file.

Deployment Resources

Service Management Resources

Windows Azure Diagnostics Resources

Troubleshooting Windows Azure Platform

This includes both the Tools & SDK as well as support for .NET Libraries & Technologies.

Windows Azure Platform Resources

Windows Azure


Troubleshooting SQL Azure

SQL Azure is a Relational Database System in the Cloud. Microsoft’s Cloud approach to the cloud does not limit support for Popular Programming Languages and therefore was a need for a Management Component for SQL Azure to allow those who are not using the Microsoft Stack a way to manage their SQL Azure database without the need to install SQL Server Management Studio (SSMS).

SQL Azure Database Troubleshooting

When Microsoft started Venturing down the road of Creating SQL Azure they had a number of security concerns to address exposing a full features SQL Server instance in the Cloud. With this in mind, there were a number of features that were pulled out of SQL Azure from your typical install of SQL Server 2008.

Along the same lines of feature support for SQL Azure, there were a number of commands that needed to be cut from T-SQL in the Cloud.

SQL Azure Resources

Transact-SQL (T-SQL) Resources

SQL Azure Manager Troubleshooting

Formerly Project Houston, the Database Manager for SQL Azure is a new service offering (Released at PDC 2010).  An extremely compelling offering being able to manage a database from any computer that has internet connectivity, with one limitation, a dependency on the Silverlight Browser Plugin (which in my opinion *everyone* should have).

Database Manager for SQL Azure Resources


Troubleshooting Windows Azure Security

Security is one of the Major concerns in the Cloud, Hopefully you aren’t using these to troubleshoot someone breaking into your application on Windows Azure, but actually reading them while you’re preparing your architecture plans for your system.

Windows Azure Security Resources

This article also appears on SyntaxC4's Blog.

Migrating Large Databases from On-Premise to SQL Azure

altRecently, I was working on a project that required a site migration from a Shared Hosting server to Windows Azure. This application has been up and running for sometime and had acquired quite a substantially sized database.

During the course of the project I ran across a few road blocks which I wasn’t expecting, due to the experience gained in my previous blog entries: Migrate a database using the SQL Azure Data Sync Tool and Scripting a database for SQL Azure (Issues explained in previous link resolved with launch of SQL Server 2008 R2). Hopefully the following tricks will help you along your data migration.

Using Import/Export in SSMS to Migrate to SQL Azure

In addition to the SQL Azure Data Sync Tool, it is possible to use the existing Import/Export Wizard in SQL Server Management Studio to migrate data to SQL Azure. There are a number of things to keep in mind while using the Import/Export Tool:

SQL Server Native Client to .NET Data Provider for SqlServer

SQL Azure doesn’t fall under the typical SQL Server Native Client 10.0 Product SKU, this means that you’ll have to use the .NET Data Provider to migrate your data. The configuration screen for the provider is very intuitive, but there are two key settings that should be changed from their default values, Asynchronous Processing (set to true) and Connection Timeout (increase to 1500).


Without changing the Timeout value the data migration would error out after creating the fist few sets of rows. Making this an Asynchronous process was beneficial when exporting multiple tables at a time.

Work-around for SSIS Type: (Type unknown …) Error

There is a chance when you go to run the migration that you will encounter an error as described in Wayne Berry’s [@WayneBerry] blog post entitled “SSIS Error to SQL Azure with varbinary(max)” on the SQL Azure Blog.

As Wayne explains in his post, there are a number of XML files which contain data mapping information used by the Import/Export Wizard in order to map the data from the source database to the proper data type in the destination database.

Database Seeded Identity Insert Issue

I’m not sure why this happened, but when using the Import/Export even with Identity Insert on, the ID [Identity] Column was not Inserting the correct values. To get around this I used the ROW_NUMBER to generate new Identities and rebuilt the foreign key tables.

There is a lot of chatter on the Forums and other blog posts that say that BCP with the –E switch is the most effective way to do exact copying (with Identity Columns).

For more information:

Cost Effective Approach

A good thing to keep in mind while preparing your database for migration is that transactions as well as data transfer costs are applied to Queries to (and from) SQL Azure. With this in mind it would be best to set up a scenario where you would test your data migration to ensure the data migration would be performed in the least number of attempts as possible.

Happy Clouding!

This post also appears on SyntaxC4's Blog