Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Implement a SaaS model where there is a centralized database which serves the application to the branches (i.e., via secured web app). If replication is too complicated, you're going down the wrong path putting a SQL Server in each branch. –  Jon Seigel Sep 1 '13 at 14:56
add comment

migrated from stackoverflow.com Sep 1 '13 at 2:36

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.