13.3.2012 | Sakari Nahi
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:
NOTE: If you enter a number in Rule Name-field, portal throws a cryptic error (e.g. “Home2″ fails)
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
5) Click “Next” until you get to choosing tables, where you shouldn’t choose any sys.-tables
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 “|”
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”
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
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 domain\user, 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
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)
3.2.7) Select database in Management Portal and Finish
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
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
Note: Sync uses additional tables in your database for syncing purposes
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
3) Wait for it.. And done!
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)
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)
6) Execute the final script and all done
And that’s it! I hope this post gave you some options and tools for moving your SQL Azure data around.