Database synchronization? Your thoughts?

Question

.NET

Everyone, good evening. In general, the task was to implement the synchronization of 2 SQL databases. Moreover, the first database is ours, and the second is third-party, and other developers smoke it. They have a data bus. They told us that we would work through it. Okay, we're working. We wrote a WCF service, posted it, they seem to be tweaking something there and the data will go through it for us, okay.
But for our part, we must stuff our data into their bus, now. We faced this for the first time, and therefore there were questions about how much this is all right with us? Questions:
1) It was decided to hang triggers on the data we need in the tables, which, after DML (I, U, D) operations, insert into the table for Import, from where, respectively, in the future we will find out what and where has been updated. How good is this method? We don't have any other option.
2) We seem to have figured out the data and now we can get it, then we need to send it to the bus. It was decided to make a windows service that polls the Import table at a certain interval, and checks if there is data or not. Based on this, we are already shipping. BUT we do not like this option, that is, it is working, but something is not impressive. We began to figure out if it could be done so that the database itself would notify us that a record was added to the Import table, then we would immediately transfer data, reduce the load, not some of these timers, etc. We found such a SqlDependency thing, it was painful for us to love her. Does anyone have experience with this stuff? What are the consequences of its use can lead to performance, security? She uses some kind of SQL Server Service Broker, can it do any harm? If any other mechanisms for notifying the application by the database?
3) I would also like to hear your comments on how you would approach the solution of this problem, with the same conditions?

Answer 1

In principle, the option with "queues" is quite convenient:
changes by triggers are laid out in the outgoing queue, then, later, entries are raked out of this queue by a certain handler, possibly with "embellishment" in the form of adding reference data to the upload, etc.
You can also store the number of attempts to send and lasterror there.
From the pros - several records for unloading the same entity can fly into the dirty queue (for example, the manager entered a comment to the client, saved, thought, corrected the comment and saved again = 2 records), and before sending, it is already possible to group by the id of the entities (unless, of course, historicity is required by logic).
Incoming queue - here actually get-add-process (accept), where, firstly, there may also be an error. Well, as an option - a certain doorbell from the "other side" after the appearance of the data - i.e. it is possible not to check the availability by timers, but to work on a signal.

Answer 2

The task of being stunned is not an easy one.
They insert two records at the same time in DB_1 and DB_2, they have exactly different id :) They
synchronized, then they start changing the same record in DB_1 and DB_2 at the same time, who will win at the next synchronization?
Taking into account the delays in data transfer between the database over the network + the reliability of this channel, the time of continuous operation is not 100%.

Answer 3

Is the database structure the same? Then the standard replication mechanism will save the father of Russian democracy. If not, then you need to write your own mechanism, but here it’s already more difficult.
Its synchronization mechanism must solve two problems - the
first: synchronization of the master-table in relation to the master-detail and conflict resolution;
second: the same for detail tables.
If there are more "levels" of detail, then there are more levels.