Azure Data Factory

salilsingh-9961 346

Hi Team,

I need to sync my on-premises SQL Server db to Azure SQL db.
Can I use Azure data factory for this purpose?
Could you please provide me with a link that shows how to implement Azure data factory for above mentioned requirement?

Thanks,
Salil

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-04-02T01:48:33.083+00:00

Hi @salilsingh-9961 ,

Just checking in to see if the below suggestions from @Nandan Hegde , anonymous userChaudhari was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.

Thank you

2 answers

Nandan Hegde 29,886 Reputation points MVP

2021-03-31T05:14:45.557+00:00

Hey @salilsingh-9961 ,
You can create Pipelines with Copy activity with On Prem SQL server as source and Azure SQL Database as sink.
https://learn.microsoft.com/en-us/azure/machine-learning/team-data-science-process/move-sql-azure-adf
https://www.sqlshack.com/copy-data-from-on-premises-data-store-to-an-azure-data-store-using-azure-data-factory/

But ideally this approach for syncing on prem and Azure SQL DB would have certain challenges as below:

1) For every table you need to sync, you need to create a copy activity mapping the source and sink
2) since the source is on prem, you would need an additional VM which would host the ADF integration run time (thereby additional cost)
3) Scheduling the jobs? you can schedule jobs daily to sync all the tables
Rather than that you can think of Azure Data sync option
Please sign in to rate this answer.

1 person found this answer helpful.
salilsingh-9961 346 Reputation points

2021-03-31T06:03:23.867+00:00

Hi @Nandan Hegde ,

I have implemented Azure data sync option, using it I was able to sync only 27 tables out of 71 tables (in SQL Server on-premises DB , DB is AdventureWorks2012). Other tables did not syns as there are limitations in Azure sql data sync.

Please have a look in the question i asked here - https://learn.microsoft.com/en-us/answers/questions/337326/azure-sql-data-sync-1.html

As above did not work in desired manner so I moved to implementing Azure Data Factory for syncing process.
As given in the link - https://www.sqlshack.com/copy-data-from-on-premises-data-store-to-an-azure-data-store-using-azure-data-factory/
i could see more then 1 table could be selected from data source, is this not possible?
Also, cant the integration run time be installed on the machine on which on-premises SQL server is installed so that no additional VM is needed?

Thanks,
Salil

Nandan Hegde 29,886 Reputation points MVP

2021-03-31T06:15:56.497+00:00

Hey,
Ideally IR can be installed on the same VM as the SQL server but sometimes based on the AD settings there might be issues of login failures (rare scenarios) : you can test it out whether yours falls in such scenarios

And w.r.t your other query:
In a copy activity , you can select only 1 source table and map it to only 1 destination table.
Since you have 72 tables which you needs to sync, you would have to create 72 different copy activities for easy 1 time development.

There can a dynamic scenario as well wherein you would need only 1 copy actvity as below:

Lookup activity( dsitinct tables names which you want to sync)
the above output would act as input to for loop activity wherein you can dynamically map source table and destnation table

Nandan Hegde 29,886 Reputation points MVP

2021-04-01T03:06:17.64+00:00

Hey @salilsingh-9961 ,
Did it resolve our query? For reference, you can refer the below documentation provided by Vaibhav
Sign in to comment
Vaibhav Chaudhari 38,576 Reputation points

2021-03-31T06:26:00.647+00:00

Refer this to copy multiple tables using ADF

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-bulk-copy-portal

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment