This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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(166.4, 81%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Im using an sql server in my on premise with version 2019, I need to transfer or replicate real time data to azure synapse database, so which azure service will be more effective and flexible for large data transfer
I need to replicate huge amount of data from multiple servers, provided methods are complex when it comes to large data, Is there any soluion for this?
Hi，AGP，We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
![](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(201.6, 18%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EGG%3C/text%3E%3C/svg%3E)
@AGP - Just checking in to see if the below answers provided by @Nandan Hegde @MikeyQiaoMSFT-0444 helped.
If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know
Yeah solution was great, but here we are using database trigger for calling the Sp through linked server. So when ever there is a connectivity issue with linked server the insert or upadate doesn't work and gives an error.
Below blog explains the scenario in detail :
where you can create a linked server and trigger the sync
Hi Nandan,
I have tried your method to replicate data from sql server to synapse, during the creation we ended up in error while creating trigger in adf. Can you please help on this
Regards
AGP
I guess that Nandan will appreciate if you disclose the text of the error message or show a screenshot of it.
Data trigger which is configured here is failing or blocking the insert when ever there is network issue with linked server, so there any alternative solution for this
Rather than writing directly to Synapse, it may be better to write to a queue table, and then have a job that reads from the queue table and write to Synapse.
Or rather than a queue table, use Service Broker, which is a built-in messaging system in SQL Server. It's perfect for these types of asynchronous updates. Granted, it is an advanced feature, and it takes some consideration to set it up. Not the least in this case where you often face connection errors. If you don't handle them carefully, Service Broker may turn off the queue.
With Service Broker, the trigger would write to a Service Broker queue, and an activation procedure would then read the data and send to Synapse.
Hi，AGP
In addition to the pipeline technology of Azure Data Factory, there is another one that I recommend you can try.
This is the official introduction and usage details for pipeline technology：
https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory
Regards
Mikey Qiao
If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.