asked
Whats the best way to replicate data without data loss between more than 2 databases
I'm testing possible solutions for database redundancy and high availability. My objective is to have various applications working simultaneous and communicating with one database only. But having the databases synchronized between them so that in case of one database goes down, all its data is not lost. ![Desired architecture][1] How can I do this in order that all databases are publishers/subscribers and for a commit to be done, must be done in all databases (a two-phase commit protocol between all databases "online")? [1]:
http://i.stack.imgur.com/N8Jhb.gif
when you say communicating with one database only, do you mean each app/client connects to one of the three databases (as in the diagram) or all clients/apps connect to one database, and in the case of failure all failover to another?
You've tagged this question with SQL2008, but can you wait until SQL2012 is released? There's a feature called AlwaysOn, that will fit your requirements quite well : This combines the 2 technologies of mirroring (to give multiple copies of the data) and clustering (to give seamless failover). A good introduction can be found here :
