0

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

2

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:

enter image description here

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

1
  • 1
    Thank you for share your experience... The solution you adopted is similar to the one we are thinking to... Unfortunately this is a very complex solution and a radical change to the current implementation... We have almost 60 tables to keep aligned... They are not even identical, so it's not so easy to determine if they are changed or not... Moreover, we can't modify the structure of a DB in an easy way, we can't add GUID or timestamp... So we are in a trouble, I suppose :/ ... – ufo Jun 15 '17 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.