We have the following scenario:
We have several SQL serves instances in distributed geographic areas (distributed buildings) (in a city or maybe globally)
All the SQL servers run one snapshot of a database
The databases should be synchronized (maybe with peer to peer replication or other alternatives)
All computers in a building connected to the SQL server that is located in the building
Connection between buildings maybe lost (for a day for example) and after servers reconnected data should be synced and all the SQL servers should have same data (and we should taking care of conflicts absolutely)
How we can handle this scenario?
(I am not a SQL server expert so it would be nice of you to explain it in more details)
Best Answer
Sounds to me like Merge Replication, considering the offline aspect. You generally have a "master" which the "clients" sync to. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-2017 I would question the need to distribute the database in the first place. Makes things soooo much more complicated, so carefully weigh the gain you get from distributing the database to the headaches you get from it.