How to get your data into (and out of) SQL Azure

B"H

Most applications that will be hosting their data in SQL Azure at this point, will be applications that are being ported from other other databases. Particularly SQL Server 2008 that has been hosted on premises.

So how do you get the data from your local SQL to SQL Azure?

What you'll find out from the various sources on the net is that you have many options. I will try writing a blog post about each one. If I missed any, please let me know.

• Write ADO.net code.
  I'll post some samples.
  
• Write code to use the BCP utility.
  The sql azure team have instructions on their blog
  
• Write BCP API code.
  Most of us are more comfortable with C# than with batch files. So automating a transfer would be more natural for us with an api.
  
• Use the "SQL Azure Migration tool"
  Download it here. It uses bcp internally & it is open source hosted on codeplex. so we can tweak it to do whatever we need. Currently a migration can not be saved and/or repeated. However, it is open source, so maybe one of us can add that functionality.
  
• Use the Microsoft Sync Framework with its new Azure capabilities
  
  
• Use SSIS - Sql Server Migration Services
  Not available in SQLExpress so I will not write about it.
  

My first step

B"H

Recently I decided to take a client to the cloud!

The client did not have reliable server space at their location (which was continuously causing problems) and we were having issues integrating their data with their partners. So we decided to move to the cloud.

Being a Microsoft shop the obvious choice was Azure.

What wasn't so obvious was some of the turbulence that we were going to hit (and continue to hit) on our journey cloudward.

So here I am typing my words into the ether, resigned to the fact they they may never be read by another human being. But with the hopes that I will be able to help at least one person navigate the journey and avoid some of the pitfalls that I encountered.

I am a programmer/developer/architect (about as far from an administrator as possible) so things should be taken from that perspective.

I will start with the issues I encounter as they arise an then get back to the first challenges when I have time. So this may not be in chronological order. Sorry no road map here. But I do hope that it will help with particular details.

I will covers issues relating to Azure (right now sql azure then on to the other offerings), SQL when applicable and the Sync Framework, which I now use for so much more but I originally discovered it while trying to migrate my data to azure.