Consolidation of data from the databases of branches in the central database with aggregation. How to implement / what to choose?

Question

SQL

Task: to consolidate data from several databases into one with preliminary aggregation and with a mark of the database from which the data came. Is it possible to implement using MS SQL Server replication? What techniques exist for doing this manually (in T-SQL)? How to deal with referential integrity (cyclic references of entities to each other)? How to ensure data consistency during sampling?

Answer 1

Here a lot depends on the conditions of the problem.
1. Whether or not there is a connection between MS SQL servers, at least once in a while.
2. The databases are small and are aggregated as a whole, or huge and it is planned not only to replenish, but also to delete records.
3. How often do you want to do this.
4. And much more.
Required: the same structure of the data to be merged, the calculation of all possible collisions that may arise during the merger. Use GUIDs as identifiers (you can use the NEWSEQUENTIALID function for new entries). Mark which record came from where, it may be necessary to store deleted records.
If your servers are connected, or at least sometimes connected, then it is best to work with replications. If not, unload the database into backups, separate data transfer files, etc., drag it to the aggregation server and merge it into a heap there.