CODEWITHSUNDEEP
sqlazuressissql-server-2012azure-sql-database
Rohit Patel
Rohit Patel

Reputation: 459

Azure Copy data from 1 db to another using SSIS

I have 2 azure sql db and I've created SSIS job to transfer some data from 1 db to another. The db has millions of records

The SSIS is hosted on premise and if I execute the package on my pc,

will it directly copy the data from 1 azure db to another on the FLY OR Fetch the data from 1 azure db to my local and then upload the data to another azure db

A trip from azure to local and again from local to azure will be too costly if I have millions of records.

I am aware of azure data sync but my requirements requires ssis for transferring particular data. Also, do azure data sync have option to sync only particular tables?

Upvotes: 3

Views: 1547

Answers (2)

Tom H
Tom H

Reputation: 47464

Running the SSIS package on your local machine will cause the data to be moved to your machine before being sent out to the destination database.

When you configure a sync group in SQL Azure Data Sync you should be able to select which tables to synchronize.

Upvotes: 4

astaykov
astaykov

Reputation: 30903

I'm pretty sure the SQL Azure Data Sync does have the option to select just the tables you need to transfer. However I don't think there is an option to do transformation over the data being transfered.

As for SSIS, I don't see how would a transfer be possible without data first coming to your premises. You have to connections established - 1 connection with the first SQL Azure, and then the other connestion with the second SQL Azure server. And SSIS will pull the data from the first stream (Connection) then push it to the second one.

I would suggest exploring SQL Azure Data Sync, as it might be the best choise for your scenario. Any other option would require data to first come on premise, then being transfered back to the cloud.

Well, there is 3rd option. You create a simple worker based on ADO.NET and SqlBulkCopy class. Put your worker in a worker role in the Cloud, and trigger it by message in an Azure queue or so. Hm. That would seem to be some of the best solution, as you have total control of what is being copied. Thus all the data will stay in MSFT datacenter which means:

  1. Fast transfer
  2. No bandwidth charges (as long as all the 3 - 2 x SQL Azure server + 1 x Worker role are deployed in same affinity group)

Upvotes: 3

Related Questions