SSIS Data Transfer from Azure SQL to On-premises SQL Server 2016

1
votes

Is there any way to transfer the large volume of data from Azure SQL to on-premises SQL Server 2016 Enterprise/Standard? The requirements prescribed as follows:

  • Weekly full database transfer
  • Daily delta transfer before midnight

I read about SSIS for Azure Blob Storage but am not sure whether it is applicable to this context.

Updated: I found an article on Azure Data Sync; according to that article, it seems doable. Please share your experiences. That would be extremely helpful. https://www.mssqltips.com/sqlservertip/3062/understanding-sql-data-sync-for-sql-server/

1
sql-serverazure-sql-databasesql-server-2016data-synchronizationazure-data-sync

1 Answers

2
votes

Weekly full database transfer

SSIS Doesn't provide a way to do Full transfer of data(i mean backup),unless you want to truncate and insert from source..

For Weekly full database transfer,i would go with SQLAzure Export/Import functionality

Refer below links for more details..

1.https://github.com/richorama/SQLDatabaseBackup
2.I need to automate SQL Azure database backup in SQL Script files. How can i do so?

Daily delta transfer before midnight

You will need a way to identify delta..so create a table with all table names and last run time

create a console application which uses bulk insert functionality,which uses above table as base and insert in onpremises