This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
An Azure service for ingesting, preparing, and transforming data at scale.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 9%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
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
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
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
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
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
Hey @salilsingh-9961 ,
Did it resolve our query? For reference, you can refer the below documentation provided by Vaibhav