Best Practice to migrate data via SFTP

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