Question :
we are developing an application for a company and this company has 3 branches, we design a SQL server for our application we can put this server in one of the branches but as the connection between these branches is over internet and this connection can be lost some times but the SQL server availability is vital so we decided to mirror the server in each branch, I need a simple solution for how should i config these servers to be mirror of each other with the same privilege to do changes in DBs, all we want to do is configuring 3 SQL server exactly with the same data. we read about merge replication but we want a simpler solution because our scenario is not really enterprise.
Answer :
If you want read and write to the local server at all three branches (“multi-master”) with site independance (that is, losing the link to a site does not stop that site from operating) then you only have one solution: Peer-to-peer Transactional Replication, an Enterprise edition feature.
Merge Replication is a multi-master solution but has a single point of contact – the distributor. If the site containing the distribution role server loses its link then the other two sites will stop replicating. They will continue to serve queries but will not replicate any changes in or out.
There are a couple of HADR tools included with SQL Server – database mirroring and availability groups – however with both of these there is only one writable copy of the database. If the site containing the current master loses its link then the failover process will cause that site to stop serving requests to the database and another site to become the new master.
Could you go with one SQLServer instance at one of the offices and then just configure VPN and join the other two in with a site-to-site VPN tunnel? That would greatly simply your architecture as long as it met your performance requirements (and possibly some SQLServer license costs).