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)


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.

  • availability is the need for distributing the database (if connection of a building lost, clients can't wait and should connected to the local instance of the database and after reconnecting changes should sync between nodes) – ssz Sep 8 '18 at 7:26
  • That rules out Availability Groups (sometimes incorrectly referred to as "Always On"). Sounds like Merge Replication is what you want to investigate and test, test, test, as per my earlier comment. – Tibor Karaszi Sep 10 '18 at 10:50

Maybe this is something you can use

configure sql server alwayson availability group on a multisubnet cluster

  • If I understand correctly when using "always on availability" or even "fail over clustering", when the primary node fails the secondary node becomes primary one and takes the former primary node place, so data always changes on one node and reflect to others (secondaries), the problem is that in the scenario I have if connection of a building lost, clients should connect to the local server and continue working (that means data on primary and at least one secondary nodes changes). any suggestion? – ssz Sep 8 '18 at 7:18

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.