Advantage Database Replication

I have a client that wants two sites to have the ability to sync databases so information at Site A can be synced with Site B so the two sites can look at the same data.

I'm not even sure of the infrastructu开发者_高级运维re required. Would a VPN required to connect the 2 databases or would an internet based database work ie/Site A to InternetDatabase and Site B to InternetDatabase. Each site copies data to it periodically and then the InternetDatabase syncs it and the Sites can then pull data down.

My other thought was something like Dropbox. If Site A and Site B use a Dropbox account to sync the ADT files etc can the database at each site then sync with those ADT files?

Thanks

If the two sites update completely different tables, then something like Dropbox might work for that. Dropbox does not synchronize/merge the contents of files. That means if both site A and site B updated some file, then you would be responsible for writing the code to merge the changes.

Advantage Database Server has support for replication built in natively, so that would likely be the simplest solution. Advantage replication is performed on a record-by-record basis and is handled asynchronously. If the target database cannot be reached, the updates are stored in a queue and processed periodically. If the connection between the two sites is open/available constantly, the lag between the source update and the replicated update is typically small but obviously depends on the network bandwidth and latency.

You could use a VPN for the connection between the two sites, but it would not be required. If you do not use some kind of VPN, though, you should make sure the communication is encrypted between the two sites (it is an option when setting up the subscriptions).

Edit For the communication, all you need is "normal" network connectivity. The primary issue is dealing with things like firewalls and NAT. With Advantage, you define which port it uses. If you use a TCP/IP connection, you would need to make sure the configured port allows inbound connections to the ads.exe process. You can use UDP as well, but if you are dealing with firewalls, it is probably going to be simpler with TCP.

Your question about duplicate keys is a good one. If both sites either add a record with the same primary key or update the same record concurrently, then it results in a conflict. There is an option to simply ignore conflicts in which case the last update wins. More realistically, you would want to write an ON CONFLICT trigger to handle the conflicts.