A friend asked me couple of weeks back how to migrate data from SQL Azure to on-premises SQL Server. I told him, and didn’t think more of it.

However, last week I upgraded my notebook with a new SSD hard drive, and while I briefly thought about cloning, I decided to re-install everything. Which isn’t a big thing at Kompozure because of good images and all the data in the cloud.

Anyhow, at some point I had to copy some existing production SQL Azure databases to my local development SQL Server. My friend’s question came back to me then, and I decided to blog about this.

Basically I can think of four different methods for cloning SQL Azure database to local SQL Server:

1) SQL Server’s SSIS (SQL Server Integration Services)

2) Command line utility bcp

3) Azure Data Sync

4) 3rd party tools like SQL Azure Backup by Red Gate or SQL Azure Migration Wizard from CodePlex

For the purpose of making a single copy/migration, my personal favorites are SSIS and SQL Azure Migration Wizard.

Before doing anything else:

• Go to your Azure Management Portal, and check that you have access to your SQL Azure from your IP (click on images to view them in full size)

SQL Azure’s Firewall configuration

NOTE: If you enter a number in Rule Name-field, portal throws a cryptic error (e.g. “Home2″ fails)

SQL Server’s SSIS

This option is good and easy, if you have SQL Management Studio in use.

1) Create your local database in SQL Management Studio

2) Right click on your database and choose Tasks > Import Data…

3) Set “.Net Framework Data Provider for SqlServer” as Data source

4) Configure data source: Password, User ID, Data Source (simply your SQL Azure FQDN) and Initial Catalog

SSIS Data Source Configuration

5) Click “Next” until you get to choosing tables, where you shouldn’t choose any sys.-tables

Choosing tables in SSIS

6) Done

Command line utility BCP

BCP (Bulk Copy Program) is good for creating your own scripts. You can also use queryout-option with SQL Azure starting with SQL Server 2008 R2.

1) Command: bcp database.schema.datatable out filename -c -U username@SQLAzureServerName -S tcp:servername.database.windows.net -t “|”

BCP for SQL Azure

Azure Data Sync

This option is good for recurring synchronization of data, and also in situations where there are multiple databases either in Azure or on-premises.

1) First “Provision Data Sync Server” in Management Portal’s Data Sync-section (simply select region)

2) Select “Sync between On-Premise and SQL Azure Databases”

Azure Sync: Begin

3) Set up sync

3.1) Name (any name goes)

3.2) Click to add an On-Premise SQL Server

3.2.1) Add a new SQL Server, and set it up for Bi-Directional

Azure Sync: Adding local SQL server

3.2.2) Download and Install a new Agent to your local SQL Server computer: when typing user credentials in installer, remember to insert them in form domainuser, even if the user is in local computer – otherwise installer gives a quite cryptic error

3.2.3) In Management Portal Specify Agent Name and Generate and Copy Agent Key to clip board

Azure Sync: New Agent

3.2.4) Launch Agent in your local computer

3.2.5) Click Submit Agent Key at top and paste copied Agent key

3.2.6) Register your local database (test connection before saving)

Azure Sync: local Agent

3.2.7) Select database in Management Portal and Finish

Azure Sync: Select local database

3.3) Click to add an SQL Azure database

3.3.1) Insert server name, database name and credentials and click Test before clicking Add

Azure Sync: Hub

3.4) Set sync schedule and conflict resolution (in my case Hub wins)

3.5) Define Dataset (Select All if you want to sync all tables, but with this it is easy to only sync a subset of tables too)

3.6) Click deploy from top and sync begins

Azure Sync: Synchronizing

Note: Sync uses additional tables in your database for syncing purposes

SQL Azure Backup by Red Gate

This free tool is great for backing up SQL Azure or storage. It seems a bit slower than other choices though.

1) Download from http://www.red-gate.com/products/dba/sql-azure-backup/

2) Insert required information to tool, select direction (backup or restore), and press Next and Start Backup

SQL Azure Backup by Red Gate

3) Wait for it.. And done!

SQL Azure Backup: Done

SQL Azure Migration Wizard

This tool rocks. And is free.

1) Download from http://sqlazuremw.codeplex.com/

2) Select Analyze / Migrate: SQL Database in first screen

3) Select Source: Server Type = SQL Azure, and fill in your SQL Azure database information (remember username@server-syntax)

SQL Azure Migration Wizard: Select Source

4) After source is configured, you get to choose tables and then wizard collects the data (with bcp

). This takes a while.

5) Target Server is our local SQL Server (instead of what wizard proposes)

SQL Azure Migration Wizard: Select Target

6) Execute the final script and all done

SQL Azure Migration Wizard: Summary

And that’s it! I hope this post gave you some options and tools for moving your SQL Azure data around.