linked server from cloud to on-prem sql server

Question

i recently migrate DB in cloud and some DB still in on-prem, i found that there is a stored procedure in cloud that is selecting the data from one of the on-prem sql server to import the data into table in cloud DB.

may i know which method is the best way to perform data extraction from on-prem to cloud.

the usage of stored procedure in cloud now is using to selecting the table from on-prem and then insert into one of the table in cloud?

any suggestion to perform above purpose?

linked server? replicate on-prem DB to cloud reguarly? (is performance good for replicate whole DB to cloud?)

Answer 1

Please consider adding that table to a Sync Group between the on-premises database and the Azure SQL database using Azure SQL Data Sync. You can configure a manual sync or an automated sync with intervals for syncing that range from seconds to days. SQL Data Sync tool is free.

Using linked servers for that purpose should result in poor performance and long running job when there is a considerable number of rows to send to Azure SQL. That job you mentioned cannot recover from transient errors as SQL Data Sync does.

Answer 2

One way is a SSIS = "SQL Server Integration Services" package to transfer the data: https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15

Other way is Cloud Azure Data Factory: https://learn.microsoft.com/en-us/azure/data-factory/