How to migrate data from sql server/sql azure directly to sql azure

Question

Hi all,

I need to migrate my data from sql server to sql azure and from a sql azure to another sql azure. in both cases, data must be migrated directly without being exported to files.

bcp or bulkcopy can migrate data but data will be exported to files before copied to sql azure database. 

Are there any ways to do those tasks?

Can SSIS do so? How does SSIS actually do? Queries all data from source database to memory then copy it to destination database?

Answer 1

·         Hi There, for the sql server to sql azure case, You can use SSIS. The data pump task may spill things to disk but data stays in memory for the most part and there isn't a need to first export, save then import.

For the SQL Azure to SQL Azure case, you could write an quick app using SqlBulkCopy in .Net. This would move the data within the datacenter and would be more economical than moving the data through bcp or SSIS through an external proxy.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Obviously SSIS for SQL Azure to SQL Azure also works through the data pump task.

thanks

 

Answer 2

Hi Cihan,

If I use SSIS for the SQL Azure to SQL Azure case. Does SSIS works as below:

1. Connect to SQL Azure 1

2. Query data, store in memory of local machine

3. Connect to SQL Azure 2

4. Copy data to Sql Azure 2

 

I am considering about using Bulkcopy for both cases. it is more convenient and flexible than SSIS.

Thank you very much.

Answer 3

Try SQLBulkCopy for faster transfers and you can do it from Visual Studio.

Another option is to use Export/Import WIZARD.

Yet another option is using Data-Tier components.

There are still others...

---

mysorian

Answer 4

SQLBulkCopy works very well ... thanks for the good idea!  Your thoughts on pros/cons of where to host the app?  For example, would it make any performance difference hosting the app at the same data center as the source or target database (assuming they are different)?

---

Mike McIver