Best Practice to migrate data via SFTP

[NullPointah]

Hi, 

I am currently working on a project that will be migrating part of an on-premise solution to the cloud (AWS). As a result, we are breaking the link of our reporting tool that feeds directly to the on-premise database (soon to be cloud based database). 

The database is a microsoft sql server and let's assume it is on a supported version (currently waiting for exact version). 

For this project, the only approved integration pattern for data transfer between on-premise and cloud is via SFTP. This is the integration pattern approved by architecture governance. 

We won't be able to connect our reporting tool to the now cloud to be DB (no direct-link, no vpn, etc). 

Understanding that SFTP may not be the most elegant solution, what would be the best way using Microsoft SQL Server features to produce file(s) that can be sent on-premise via sftp and use these to:
1.- Load directly to the reporting tool. 
or

2.- Use to populate a replica of the cloud DB on-premise. 

We would be interested in changes only (not dumping the entire DB every time). 

Given my limited knowledge on the technology, I can think of a differential backup, send it via sftp, restore on-premise. Have the backup scheduled for every x min? The deltas won't be gigabytes and they will actually be small (at least that is the expectation).

Thanks 

[#2]

---

The only approved integration pattern for data transfer between on-premise and cloud is via SFTP. 

---

@NullPointah , why do you think so? Of course you can access server running in the cloud over internet, equal to an on-premise server.

And no, SFTP is no standard way (even because by default there isn't on on the target server in the cloud).

Take some time to read articles about colud/SQL Azure etc.

[#3]

Hi @olafhelper - I meant to say that when the project solution was designed, they only approved SFTP as the only integration pattern for transfering data back and forth between on-premise and cloud (this is not directly related to DB connections, but in general).

There won't be a VPN, direct-link, etc. Only the SFTP pattern is available, hence this question is about navigating through these technical limitations and constrains if that makes sense.

In other words, if you were constrained by only having an SFTP channel, what would be your best proposal for extracting the incremental data of the cloud microsoft sql server so it can be brought into on-premises at an acceptable rate, latency, etc.

[#4]

@NullPointah 

they only approved SFTP as the only integration pattern for transfering data back and forth between on-premise and cloud

---

That's nonsense and nosense is always difficult to implement.

[#5]

@NullPointah 

This isn't really a best practice question but an integration question.

Here's a couple of quick points on the concept of sending back-ups in either direction (doesn't matter which way):

1. If you're running Azure Database or Azure SQL Managed Instance, you can't ship back-ups between those and any SQL Server version other than SQL Server 2022 (which I'd wager isn't the version your on-premise host is running);
2. If the AWS-hosted database isn't SQL Server, well, clearly this isn't even possible;
3. If you're talking about an AWS-hosted IaaS installation - and I believe you are - then you really should be running the same version of SQL Server on both sides (i.e. on-premise and IaaS) if you want bi-directional capability. You can ship back-ups from the older version to the newer but not vice versa, which I believe will be a problem for you since it's most likely the AWS installation will be newer than the on-premise version;
4. Because of point 3, you may need to look at leveraging an SQL Server Integration Services package, but that's more complex than can be easily discussed here.

To re-iterate, I don't expect you're dealing with points 1 and 2, which I included purely for completeness. I have a feeling you're going to end up at point 4 from your description and client-imposed limitations.

SFTP itself isn't relevant to this discussion, since really what you're looking to achieve is an offline faux replication process. SFTP is just an old data transfer protocol - it could have been any of a number of others.

Your client is making what should be relatively trivial unnecessarily difficult by adopting an offline approach. You have my condolences.

Cheers,

Lain