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...