I need to create a process to extract data from SQL Server in Azure VM to an on-prem SQL Server. The options are as follows:
- Create a Site-to-Site VPN. There is a security risk with this.
- Download data from the Azure VM, drop them into text files and then SFTP them across at certain points in the day to the on-prem environment.
- sfTP the entire db over on a nightly basis.
- Use Azure Service Bus. This will take time to implement and may be unnecessary for this data transfer.
- Use Azure SQL Data Sync. However, this may not be applicable as the SQL Service is in a VM.
I'm looking at transferring about 20GB of data initially. Then the data load will be minimal (based on changes), this equates to about 50 different tables.
Let me know the best option or if there are any other options.