MS SQL servers in different countries linked for ETL via SP The title could be a bit complicated, but the question is very simple...
We developed an application which uses a proprietary database (call it DB_ONE). Then, there's another application with its own database (call it DB_TWO). Some data stored in DB_TWO are useful for DB_ONE (in the way they already are or with a little bit of modifications). So we developed some stored procedures to extract data from DB_TWO, trasform them and load them in DB_ONE (like an ETL process). We must maintain the data aligned, so the SPs are called quite often.

We first developed this solution thinking that the two databases would reside in the same SQL Server, but we used synonim with four part name convention to support different servers. In the last case, the servers must be linked and we observed that performance were good, with no modifications or just little modifications to the SPs. This happened when the data weren't so much or in a LAN.

Now the problems start... We would like to use the same solution with a customer having the DB_ONE in a country and DB_TWO in a different one. The amount of data is very big and in the country of DB_ONE the connection quality is not that good... The solution still works, but it requires a lot of bandwidth, the execution time is long and the synchronization can't happen to often (which is bad for some SPs).

Do you have any suggestion to improve the quality of the solution without adopt a different approach (like HTTPS compressed data exchange)...? Is it possible to compress the data, optimize the load and/or the transfer? In this scenario, the encryption of data over the WAN would also be useful...
I had to work around a similar issue where the two locations where 13,000 kilometers away and the internet connection on one of the locations was 1995 level of poor:



(Forget about merge replications and all other fancy methods). 

So after all kinds of test including Azure and other cloud services I realized I was gonna have to go with something far less orthodox.

I created a new custom TCP/IP service (basically a silent console application that runs in the background on a separate thread than the users frontend) using C# that basically queries the database on either end and finds modified, deleted and new records every 15 minutes (using GUID to make sure records on both sides have same identifiers) it will then compress and encrypt this data and send it to the other location where a queue table with timestamps will determine whether or not the records are to be committed. If a record on the receiving instance has a newer timestamp that the record being brought in (accounting for a 4 hour difference) then they go into a conflict resolution routine. 

Then some modifications to the users frontend where necessarily mainly so they're aware of data changing on one side or new records added to the database.

All of this was possible because they only needed 3 tables to be doing this cycle, I will not recommend it for drastically more complex scenarios.