question

salilsingh-9961 avatar image
0 Votes"
salilsingh-9961 asked KranthiPakala-MSFT commented

Azure Data Factory

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

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @salilsingh-9961,

Just checking in to see if the below suggestions from @NandanHegde-7720, @VaibhavChaudhari 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

0 Votes 0 ·
NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered NandanHegde-7720 commented

Hey @salilsingh-9961 ,
You can create Pipelines with Copy activity with On Prem SQL server as source and Azure SQL Database as sink.
https://docs.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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NandanHegde-7720,

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://docs.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


0 Votes 0 ·

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

0 Votes 0 ·

Hey @salilsingh-9961,
Did it resolve our query? For reference, you can refer the below documentation provided by Vaibhav

2 Votes 2 ·
VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered

Refer this to copy multiple tables using ADF

https://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.